Expression based on summation of past months

[#1]

Hi

I am having difficult making an expression a little cleaner, and easy to maintain.  I have an excel doc I'm replicating in QlikView that takes a money value for a month, and spreads it over a year, and then shows for that month how much is being earned in the month - it's not a true earned premium calculation though....

Basically, for any given month you will take the premium of the same month last year, and divide it by 24.  You will then take the premium for the current month and divide by 24.  You will then take all the months between, but this time divided by 12, and then add them together to give the EP figure.

In the application I have created left joins in the script to create the prior month values, and then in the EP expression I have done the calcs to bring the value together,  The value is correct, but I'm certain there is a cleaner way to do in without the aliasing joins and the clunky expression.  Any ideas?

Cheers!

[sunny_talwar · Accepted solution]

I think you idea is right, but I would not join it back to your main table. You can rather use the The As-Of Table‌ here which would keep your data model more proficient

[#3]

ah thanks  - I'll check that out. i thought there may be some mixture of fun functions to use .. you know .. ones I don't know about, that might build the calc on the fly rather than having to sort it in the script

cheers