Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm trying to make a formula to sum all values that satisfies a certain condition. My task is to sum all values in the current year, given that it had a positive value in the previous year. This works fine in a pivot table with all groups on the rows (A, B, C etc) and years in the columns. But now I need to sum all those values into one row. I can make the following tables in QlikView:
Table I: =sum(variable1)
Dimension 1 | Year | 2013 | 2014 | 2015 | 2016 | 2017 |
A | 1 | 1 | 2 | 3 | ||
B | 2 | 2 | 1 | |||
C | 5 | 4 | 3 | |||
D | 1 | 1 |
Table II: =if(before(sum(variable1))>0, sum(variable1))
Dimension 1 | Year | 2013 | 2014 | 2015 | 2016 | 2017 |
A | 1 | 2 | 3 | |||
B | 2 | 1 | ||||
C | 4 | 3 | ||||
D | 1 |
Now I want to do another table wich shows the total sum. So for 2015 I only want 1+4=5 and not 1+2+4=7. For 2016 I want 2+2+3=7 and not 2+2+3+1=8.
Thank you!
May be try this:
=Sum(Aggr(if(Above(sum(variable1))>0, sum(variable1)), Dimension1, Year))
The only concern here would be sorting of Year.... if you have QV12 or above you can resolve that using The sortable Aggr function is finally here! otherwise you will have to fix the sorting of the Year field in the script
May be try this:
=Sum(Aggr(if(Above(sum(variable1))>0, sum(variable1)), Dimension1, Year))
The only concern here would be sorting of Year.... if you have QV12 or above you can resolve that using The sortable Aggr function is finally here! otherwise you will have to fix the sorting of the Year field in the script
Thank You Sunny! This works great on the QV-file I attached. Unfortunately, it does not work on my main QV. Is there any other way I can try?
You can try with The As-Of Table approach....
Also can create a list box object for the Year field and on the Sort tab uncheck everything except Load Order and set it to Original. Check if the list box shows in ascending order or not... if it doesn't then you will have to fix the order in the script for QV11.2 or lower.... or you can use Aggr() sorting for QV12
Now it works!! Thank You very much!
Now it works? What did you change to make it work?
=Sum(Aggr(if(Above(sum(variable1))>0, sum(variable1)), Dimension1, (Year, (NUMERIC, ASCENDING))))
Super.... so you are using QV12 or above..... Great...