Rate Calculation based on Previous Term Amount

Fri, 28 Jan 2011 03:55:05 GMT

Hi All,

I have a typical requirement which is intersting and challenging.

Every month some support gets cancelled for a product. This rate of cancellation has to be calculated with respect to the received amount against that prodcut. this calculation is different for each level(Month, term, quarter and year) of time.

Month calculation = cancellation amouunt of that month/Average of Account receieved amount of previous term.

Hence for 200504, cancellation rate = cancellation amount of 200504/Avg. of previous term

Average of previous term = (sum(Account receivable amount) of previous terms six months)/6 i.e (Amount of 200410+Amount of 200411+amount of 200412+amount of 200501+amount of 200502+amount of 200503)/6

For Quarter = (((Cancellation amount of 1st month of Quater*3) + (CA of 2nd month of Quarter*2)+(CA 3rd month of Quarter*1))/3)/ Avg. Receivable Amount of previous term

for Term = (((Cancellation amount of 1st month of Term*6) + (CA of 2nd month of Term*5)+(CA 3rd month of Term*4)+(Cancellation amount of 4th month of Term*3) + (CA of 5th month of Term*2)+(CA 6th month of Term*1))/6) / Avg. Receivable Amount of previous term

For Year the calculation is entirely different. will put in my next post.

Please let me know how can i get the previous term average. I heard that SET analysis can be done for this type of requirement. Not sure about how to proceed.

Please help me in this regard.

topic Rate Calculation based on Previous Term Amount in QlikView

Rate Calculation based on Previous Term Amount