Expression to count the number of months to meet threshold

[#1]

Help please.

My data inclues a dollar balance at a certain month, say Dec.  It also includes dollars for each of the past 12 months.  I need to figure out how many months are needed to add up to the balance.  Let me illustrate:

Dec balance:  $50

Dec: $10

Nov: $10

Oct: $10

Sep: $10

Aug: $10

.

.

.

Jan $10

The answer to this is 5 months, since it takes 5 months to add up to the $50 balance.  If my balance at Dec was $42, the answer would be 4.2 months.

My data is structured in columns beginning with the dec balance and then the month dollars after that.

Any ideas on how to do this expression in Qlikview?

THANKS!

[whiteline]

Hi.

To do this you need to calculate cumulative sum. The actual way depends on your requirements.

General solution is to use AsOfTable and always get the result with respect to the selections, it also allows to choose threshold balance dynamically.

But you can just calculate the flag in script using peek/previous functions. And then use it in front end.

Or you can create a chart table and use inter-record functions above/rangesum to calculate the rolling sum. Then use it to highlight the # of months to threshold for user. But you have to show all that months in separate lines.