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
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