Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum based on a condition for a lagged value

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 1Year20132014201520162017
A1123
B221
C543
D11

Table II: =if(before(sum(variable1))>0, sum(variable1))

Dimension 1Year20132014201520162017
A123
B21
C43
D1

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!

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

8 Replies
sunny_talwar

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

Not applicable
Author

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?

sunny_talwar

You can try with The As-Of Table approach....

sunny_talwar

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

Capture.PNG

Not applicable
Author

Now it works!! Thank You very much!

sunny_talwar

Now it works? What did you change to make it work?

Not applicable
Author

=Sum(Aggr(if(Above(sum(variable1))>0, sum(variable1)), Dimension1, (Year, (NUMERIC, ASCENDING))))

sunny_talwar

Super.... so you are using QV12 or above..... Great...