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?
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
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.
