topic Re: Relationship between TOTAL and Set analysis in QlikView

Relationship between TOTAL and Set analysis

Anonymous — Thu, 09 Aug 2018 07:49:01 GMT

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.

Re: Relationship between TOTAL and Set analysis

sunny_talwar — Thu, 09 Aug 2018 16:55:41 GMT

May be you need this

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

Re: Relationship between TOTAL and Set analysis

Anonymous — Fri, 10 Aug 2018 08:44:29 GMT

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

Re: Relationship between TOTAL and Set analysis

sunny_talwar — Fri, 10 Aug 2018 11:09:34 GMT

In that case... try this

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

Re: Relationship between TOTAL and Set analysis

Anonymous — Mon, 13 Aug 2018 07:33:37 GMT

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.

Re: Relationship between TOTAL and Set analysis

sunny_talwar — Tue, 14 Aug 2018 19:50:14 GMT

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