If you are unable to create a new account, please email support@bspsoftware.com

 

YTD, STD, PTD, filters etc based on 4-5-4 retail calendar date table

Started by patrickthatcher, 22 Apr 2015 07:30:45 PM

Previous topic - Next topic

patrickthatcher

Hello.  We use a 4-5-5 retail calendar and I'm having a hard time trying to setup filters for Year to Date (YTD), Season to Date (STD), etc and it because I cannot figure out the best way to extract the year for a given date.  The problem is one cannot just extract the date and always be correct.
Example:
Year 2015
Starts 2/1/2015 : Ends 1/30/2016

So if we do the following extract(year, 4/22/2015), we get 2015 and this is correct.  But if we do extract(year, 1/12/2016), we get 2016 which is incorrect because it is still 2015 according to our calendar.
Here's a partial setup of our Date table:
Date     (1/19/2016)
Year     (2015)
Period Key (201512)
Week Key   (2015123)
GREG_AMC_YR_BGN_DT (2/1/2015)
GREG_AMC_SEASN_BGN_DT    (8/2/2015)
GREG_AMC_PRD_BGN_DT (1/3/2016)
GREG_AMC_PRD_END_DT (1/30/2016)
GREG_AMC_WK_END_DT (1/23/2016)
GREG_AMC_WK_STRT_DT (1/17/2016)

If I were to do this in SQL, I would query the date table with the date and pull back the year. I'm not sure how to do this in a filter

Here's what I have so far.  This bounds the upper end of the date to yesterday, but lower date pulls back all the available dates prior to yesterday.

YTD: [Model View].[Date Dimension].[GREG_AMC_YR_BGN_DT]  <= _add_days ( current_date,-1) AND
[Model View].[Date Dimension].[Date]  <= _add_days ( current_date,-1)

Thanks for any help provided

patrickthatcher

I've decided to try a different way. Is it possible to set the year value based on a Parameter Map?  I created map user_LookUpYear based on my date table. The key is Date and the return value is Year.

So now my standalone filter is:
YTD: [Model View].[Date Dimension].[Year]  = #$userLookup_Year{$current_date}#   AND
[Model View].[Date Dimension].[Date]  <= _add_days ( current_date,0)

However, the lookup only shows the default value and I'm not sure why

The Key Value appears as such:
Feb 3,2013  -> 2013