topic Future invoice amount in QlikView

Future invoice amount

Tue, 01 Sep 2009 19:25:07 GMT

Hi all,

My challenge is as follows.. My financial department wants an overview of the amount of money they can invoice our customers in the future.

For example :

Customer A has a Contract for 3 years paying 1.000€ each month. At the start of this contract the total restworth of this contract is 36 K€. When i set my date to 1,5 years (18 Months) in the future It should state that the restworth of that contract is 18 K€.

Offcourse we have mulitiple contracts all with different Start and end dates. But i think the principle is the same for 1 contract or a thousand.

I was thinking i had to create a calender object and make a table witch contains all the future invoices. and just count them.. But that table will be enourmous and i think (hope) that there will be a better way..

Anyone have any ideas on the subject? Or perhaps has created something similar?

Thanks!

Laurens

Future invoice amount

Tue, 01 Sep 2009 21:23:17 GMT

When you are loading the script have you a contract end date. If yes then you can use the following in your expression

=sum(((date(month(contract_end_date),'MMM-YYYY') - date(month(today()),'MMM-YYYY'))) *Mth_Contract_Price)

sum((date(month(contract_end_date),'MMM-YYYY') - date(month(today()),'MMM-YYYY') calculates the difference in months between today and the contract end.

you can substitute today() for something else if your anchor date is not today

Future invoice amount

johnw — Wed, 02 Sep 2009 02:47:06 GMT

I suspect that you'll be best off just converting the contracts to data points in the script, like you appear to have been thinking. Aggregating a calculation across a thousand contracts every time someone looks at the chart sounds a lot worse to me than just creating data points for a thousand contracts for each month for a few years. Now, if those contracts are daily, or hourly, or every tenth of a second, I could see how it could be an enormous table, and you might want to go with a calculation. But as described, the table doesn't sound very big to me.