Forum Discussion
Distributing multiple values over different time periods
Most of what you're saying makes complete sense to me.
Just to recap what we can currently do and your goals:
- Currently, you can calculate the per day value of a contract
- This is done by taking the total value of a contract and dividing it by the number of working days between TODAY() and the contract end date
- ^ this is the part that doesn't make total sense to me. Is the total value of a contract updated daily? See my example below that explains my confusion.
- You can also count the working days in a given timeframe with your time dimension
- You have the option take the per day value of a contract and multiply it by the number of working days in a given timeframe
- This could be a single month, a week, now until the end of the contract, etc
I guess what I'm not understanding is why you're using TODAY() to calculate the remaining value of a contract. Please correct me, but I would assume the value of Contract LMNO is constant. If contract LMNO is a week long with a worth of $10,000, there would be 5 working days between contract start and end dates to split the $10,000.
However, if you use the number of working days between TODAY() and the contract end date, on the first day of the project, each day would show $2000, but the 2nd day there would only be 4 working days between the new TODAY() and the end date, meaning each day has a value of $2500, even though the contract value hasn't changed.
If the remaining value of a contract is updated daily, please let me know and ignore this whole section, as your setup makes much more sense if that's the case.
Finally, how do you want all of this displayed in a visual? It seems that you want to create a timeline as your X-axis, and sum up the per day value of all active contracts. If this is not correct and you want to display this in some sort of table visual, please let me know.
Sorry for not clarifying this earlier - the contracts are indeed updated daily. So the value of a contract is only showing the open amount that we can expect in the future.
e.g. we do a contract with total value 12k with expiry x. As the time goes by, the customer orders goods that are covered by the contract and each order is reducing the total value of the contract. So after some time the value of the contract is down to 5k for example. Those 5k represent the value that the customer still has to order before expiry.
The visualisation is planned like you described it. Having a timeline and showing the distributed values along.
So then yeah. I think in that case that the previously proposed solution should work. Is it giving you bad results?
Calculating the daily value of the contracts for the upcoming working days until expiry is working.
But I still can`t figure out how to distribute these values to future dates.