How to calculate revenue to each month?

[#1]

Hello Everyone,

Our database store revenue by date range? I need calculate this revenue to each month. Do you have any idea how to allocate revenue to each month?

Original Data:

	Start Date	End Date	Revenue
Customer 1	3/2/2014	3/6/2014	100
Customer 2	3/1/2014	3/8/2014	80
Customer 3	3/4/2014	4/2/2014	1320

Need Data:

Revenue	March	April	Revenue Total
Customer 1	100	0	100
Customer 2	80	0	80
Customer 3	1240	80	1320

Thank you very much in advance!

Dust

[swuehl · Accepted solution]

Distribute the Revenue across calendar dates like this:

Set DateFormat = 'M/D/YYYY';

INPUT:

LOAD * INLINE [

Customer, Start Date, End Date, Revenue

Customer 1, 3/2/2014, 3/6/2014, 100

Customer 2, 3/1/2014, 3/8/2014, 80

Customer 3, 3/4/2014, 4/2/2014, 1320

];

LOAD *, Month(Date) as Month;

LOAD Customer,

  Date([Start Date]+iterno()-1) as Date,

  Revenue/([End Date]-[Start Date]+1) as Amount

Resident INPUT

while ([Start Date]+iterno()-1) <= [End Date];

Then creating a chart with dimensions Customer and Month, and expression sum(Amount) should do.

[#3]

Thank you very much - swuehl!

Your solution works like a charm.

Wish you all the best!