# 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 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!

Tags (4)
1 Solution

Accepted Solutions MVP

## 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 MVP

## 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? MVP

## Re: Sum based on a condition for a lagged value

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

## 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  Not applicable

## Re: Sum based on a condition for a lagged value

Now it works!! Thank You very much! MVP

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

## Re: Sum based on a condition for a lagged value

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