Running total with flexible start

[#1]

Hi. I need to calculate a type of running total which restarts at certain conditions. As an example, think of a regulary reccuring questionnary where you want to follow up each respondents historic answers. In the data example below the three first column are loaded from source and I need to calculate the last. The logic behind it is that as it should count how many reccuring years a specifc question has failed.

Question, Year, Failed, FailCount
1, 2009, 1, 2
1, 2008, 1, 1
1, 2007, 0, 0
1, 2006, 1, 1
2, 2009, 0, 0
2, 2008, 1, 3
2, 2007, 1, 2
2, 2006, 1, 1

It's an easy thing to solve with a loop and a shadow variable, but I can't get QlikView to loop over a previously loaded table. I've tried to use the function previous() in the load but without success. If it's possible to solve the problem directly in chart it's also ok.

Any suggestions?

/Martin Höglund

[stephencredmond · Accepted solution]

Hi,

Try this:

TempData:
Load * Inline [
Question, Year, Failed
1, 2009, 1
1, 2008, 1
1, 2007, 0
1, 2006, 1
2, 2009, 0
2, 2008, 1
2, 2007, 1
2, 2006, 1
];
Data:
Load
 Question,
 Year,
 Failed,
 If(Question = Previous(Question), Failed + Peek('FailedCount',-1,'Data'), Failed) As FailedCount
Resident
 TempData
Order by Question, Year;
Drop Table TempData;

Stephen

[#3]

Thanks!

It almost did it. A slightly change and it all works perfect!

If(Question = Previous(Question), if(Failed = 0, 0, Failed + Peek('FailedCount',-1,'Data')), Failed) As FailedCount

/Martin