topic Re: Sum based on a condition for a lagged value in QlikView

Sum based on a condition for a lagged value

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Sum based on a condition for a lagged value

sunny_talwar — Wed, 03 May 2017 11:59:50 GMT

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

Re: Sum based on a condition for a lagged value

Wed, 03 May 2017 12:26:47 GMT

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

sunny_talwar — Wed, 03 May 2017 12:29:55 GMT

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

Re: Sum based on a condition for a lagged value

sunny_talwar — Wed, 03 May 2017 12:35:29 GMT

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

Re: Sum based on a condition for a lagged value

Wed, 03 May 2017 12:42:47 GMT

Now it works!! Thank You very much!

Re: Sum based on a condition for a lagged value

sunny_talwar — Wed, 03 May 2017 12:48:08 GMT

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

Re: Sum based on a condition for a lagged value

Wed, 03 May 2017 12:51:13 GMT

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

Re: Sum based on a condition for a lagged value

sunny_talwar — Wed, 03 May 2017 12:52:15 GMT

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