Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I have a scenario here and bit confused in solving these
Till now i was calculating the YTD value , basically if i select week 4 and year 2017 then i will be getting the cumulative sum from week 1 till week 4 of 2017 and my expression was
Count({<WEEK = {"<=$(=Max({<YEAR={$(=MAX(YEAR))}>} WEEK_M))"},YEAR = {"<=$(=Max(YEAR))"}>}Value)
Now the current scenario is that if i select week 4 and year 2017 then the values displayed should be from the beginning of the data till the selected parameters ie till week 4 of 2017
so if i have the data starting from week 01 and year 2014 and i have selected the parameters as week 4 and year 2017 the the value should be sum or count from week 1,2014 till week 4,2017
Please suggest
I would include in my calendar or fact table a week sequence number (numbered starting at 1 from a defined start date). For the sake of discussion, let us call it WEEK_Seq. Then your set analysis expressions become much simpler; something like this for YTD:
Count({<
WEEK,
WEEK_Seq = {"<=$(=Max(WEEK_Seq))"},
YEAR = {"<=$(=Max(YEAR))"}
>} Value)
And Life to date:
Count({<
WEEK,
WEEK_Seq = {"<=$(=Max(WEEK_Seq))"}
>} Value)
I am assuming that you are making a selection on WEEK, hence I have included an override.
I would include in my calendar or fact table a week sequence number (numbered starting at 1 from a defined start date). For the sake of discussion, let us call it WEEK_Seq. Then your set analysis expressions become much simpler; something like this for YTD:
Count({<
WEEK,
WEEK_Seq = {"<=$(=Max(WEEK_Seq))"},
YEAR = {"<=$(=Max(YEAR))"}
>} Value)
And Life to date:
Count({<
WEEK,
WEEK_Seq = {"<=$(=Max(WEEK_Seq))"}
>} Value)
I am assuming that you are making a selection on WEEK, hence I have included an override.
Hi Jonathan,
I am making a selection on week and year. should i include year as well for life to date calculation?
Your are correct - you will need a YEAR override on the life to date:
Count({<
WEEK, YEAR,
WEEK_Seq = {"<=$(=Max(WEEK_Seq))"}
>} Value)