Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
marek_polasek
Contributor
Contributor

Above with inner aggr - ignores inner selection

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.

1 Solution

Accepted Solutions
sunny_talwar

Is this what you are trying to get?

Capture.PNG

May be use this

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

View solution in original post

7 Replies
vishsaggi
Champion III
Champion III

‌mmay be try like

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

sebastiandperei
Specialist
Specialist

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))

marek_polasek
Contributor
Contributor
Author

Adding Month to outer expression has no effect.

marek_polasek
Contributor
Contributor
Author

Sebastian,

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

sebastiandperei
Specialist
Specialist

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.

sunny_talwar

Is this what you are trying to get?

Capture.PNG

May be use this

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

marek_polasek
Contributor
Contributor
Author

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.

M.