Relationship between TOTAL and Set analysis

[Anonymous]

Hello everyone

I was working with some dates, and the client asked me to represent in a chart:

• X Axis: Year-Month (Concatenation between year and month. Format 'YYYYMM')
• Y Axis: CY / LY, being
  • CY: Current year values calculated for each month. A simple Sum(VALUE)
  • LY: Last year values calculated for the same month as the CY. Same formula, Sum(VALUE)

Doing that for the months of the actual year is quite easy (You get the month as dimension and the CY and LY in set analysis), but then they asked me to do it for the last 12 months.

I tried to do Sum(VALUE) / Sum(TOTAL {< YearMonth={'$(=Max(YearMonth-100))'}>} VALUE), but the TOTAL also affects the set analysis.

Can anyone give me a possible solution for this problem? Thanks in advance,

Pedro.

[sunny_talwar]

May be you need this

Sum(VALUE)/Above(Sum(VALUE), 12)

[Anonymous]

Hi Sunny,

Thanks for your answer. I have checked and the formula that you have given me works because I only have 24 months  of data and I can choose not to include the zero values.

If my case were different and I had more than 24 months, I would have to hide some values by limiting the amount of YearMonths displayed (either hardcoded in the dimension or by limiting it by a Fixed number). In this case, the above formula doesn't work because the values for the last year dissapear too.

Anyway, for me it works. Thanks!!

PD: I will mark your answer as correct, but I'm going to leave some time to see if anyone can give a solution that also works for more than 24 months

[sunny_talwar · Accepted solution]

In that case... try this

Sum(VALUE)/Above(Sum(VALUE), 12) * Avg({< YearMonth={'>$(=Max(YearMonth-100))'}>} 1)

[Anonymous]

Hi stalwar1‌,

Very smart answer indeed. The formula worked with a small modification to include the last 12 months, and not only the 12th

(Sum(KPI_VALUE)/Above(Sum(KPI_VALUE),12)) * Avg({< YearMonth={">$(=Max(YearMonth-100))"}>} 1)

I will mark your answer as the correct one. Thanks,

Pedro.

[sunny_talwar]

Made correction in my expression to avoid any confusion for future visitors