Values from the start till the week and year selected as a parameter

[ishanbansal1204]

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

[jonathandienst · Accepted solution]

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.

[ishanbansal1204]

Hi Jonathan,

I am making a selection on week and year. should i include year as well for life to date calculation?

[jonathandienst]

Your are correct - you will need a YEAR override on the life to date:

Count({<

  WEEK, YEAR,

  WEEK_Seq = {"<=$(=Max(WEEK_Seq))"}

  >} Value)