topic Running total with flexible start in QlikView

Running total with flexible start

Thu, 17 Sep 2009 19:10:03 GMT

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

Running total with flexible start

stephencredmond — Thu, 17 Sep 2009 19:58:14 GMT

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

Running total with flexible start

Thu, 17 Sep 2009 20:28:30 GMT

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