Forum Discussion
Visualize a complex monthly sumproduct
Hi menelaos1983,
For your sample data
Client A | 2017-01-01 | 2017-01-12 | $20,000
Client B | 2017-01-12 | 2018-01-01 | $12,000
Client C | 2016-05-01 | 2016-09-12 | $13,200
you need the result
Client A: 12 days * $20,000 = $240,000
Client B: 20 days * $12,000 = $240,000
Client C: 0 days * $13,200 = $0
Total = $480,000
I cannot understand why Client A is 12 days, Client B is 20 days and Client C is 0 days. Please elaborate the logic for your calculation, so that we can make further analysis.
Regards,
Charlie Liao
Sure thing. I'm trying to create a bar chart that shows the value of our jobs pipeline per month, for the next 12 months.
First month is Jan 2017, last is Dec 2017 (it should automatically select the right months, but let's stick to Jan-Dec 2017 for this example).
Now for EACH month we need to calculate the value of the pipeline.
For January:
- Client A will give us $20,000 per day * 12 days (from 1st until 12th of the month)
- Client B will give us $12,000 per day * 20 days (from 12th until 31st of the month)
- Client C will give us nothing (since his contract has already finished in Sep 2016)
For February:
- Client A will give us nothing (his contract finished in Jan 2017)
- Client B will give us 12,000 * 28 days (from 1st to 28th of the month)
- Client C will give us nothing (since his contract has already finished in Sep 2016)
For March:
- Client A will give us nothing (his contract finished in Jan 2017)
- Client B will give us 12,000 * 31 days (from 1st to 31st of the month)
- Client C will give us nothing (since his contract has already finished in Sep 2016)
...etc etc
What I want to visualize is the sum of all contracts per month, ie. a bar chart with 12 bars.
First bar will be Jan 2017, and the value will be 480,000
Second bar will be Feb 2017, and the value will be 336,000
Third bar will be Mar 2017, and the value will be 372,000
...etc
I hope this clarifies. Any ideas?
Many thanks!
Cheers,
menelaos