topic Re: Above with inner aggr - ignores inner selection in QlikView

Above with inner aggr - ignores inner selection

marek_polasek — Wed, 25 Nov 2020 16:16:04 GMT

Let's have simple model: Month, Product, Sales.

The task is to create a table (or graph) showing for each month the sum of sales for months M-2, M-3, M-4.  For example: on June-2017 we want to show  sum of sales for three months (April-2017, March-2017, February-2017). The chart shall reflect our selection in month and products. This means that if we select only June-2017, three month data (which is not selected) should be taken for calculation.

I tried to solve this by expression:

Sum(Aggr(RangeSum(Above(Aggr(sum({$<Month=>}Sales),Product,Month),2,3)), Product, Month))

which gives correct result but takes data for calculation only from selected months.

Re: Above with inner aggr - ignores inner selection

vishsaggi — Sat, 03 Mar 2018 20:10:33 GMT

‌mmay be try like

Sum({<Month>} Aggr(RangeSum(Above(Aggr(sum({$<Month=>}Sales),Product,Month),2,3)), Product, Month))

Re: Above with inner aggr - ignores inner selection

sebastiandperei — Sun, 04 Mar 2018 00:57:25 GMT

Hi!

Create one Alternate State (Document Options, General, Alternate states). We will call it "aux" in the example.

- First, you need to duplicate Month listbox and asign to the copy the "aux" state, then clear selection and then erase the copy

- Now, asign "aux" state to the graph

- Edit the Month dimention. It must be: =aggr(only({$}Month),Month)

- The expression must be: Sum(Aggr(RangeSum(Above(Aggr(sum({sss}Sales),Product,Month),2,3)), Product, Month))

Re: Above with inner aggr - ignores inner selection

marek_polasek — Thu, 08 Mar 2018 09:28:47 GMT

Adding Month to outer expression has no effect.

Re: Above with inner aggr - ignores inner selection

marek_polasek — Thu, 08 Mar 2018 09:39:08 GMT

Sebastian,

thank you for your response. However your suggestion does not work. I am affraid you did not try it.

Re: Above with inner aggr - ignores inner selection

sebastiandperei — Fri, 09 Mar 2018 11:13:29 GMT

Yes Marek, i have tested and it worked.

Im affraid YOU haven't did it conscientiously. I was made a mistake in expression in the name of the state.

Re: Above with inner aggr - ignores inner selection

sunny_talwar — Fri, 09 Mar 2018 12:26:43 GMT

Is this what you are trying to get?

May be use this

Sum(Aggr(RangeSum(Above(Sum({$<Month>}Sales), 2, 3)), Product, Month))

Re: Above with inner aggr - ignores inner selection

marek_polasek — Wed, 21 Mar 2018 16:37:12 GMT

Thank you, I'd rather prefer the solution of Sunny Talwar. It works with selection in other dimensions (like selecting Product) without any complications in alternative states.