Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
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

Re: Sum based on a condition for a lagged value

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

8 Replies

Re: Sum based on a condition for a lagged value

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

Re: Sum based on a condition for a lagged value

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?

Re: Sum based on a condition for a lagged value

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

Re: Sum based on a condition for a lagged value

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

Re: Sum based on a condition for a lagged value

Now it works!! Thank You very much!

Re: Sum based on a condition for a lagged value

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

Not applicable

Re: Sum based on a condition for a lagged value

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

Re: Sum based on a condition for a lagged value

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