Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to calculate revenue to each month?

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 DateEnd DateRevenue
Customer 13/2/20143/6/2014100
Customer 23/1/20143/8/201480
Customer 33/4/20144/2/20141320

Need Data:

RevenueMarchAprilRevenue Total
Customer 11000100
Customer 280080
Customer 31240801320

Thank you very much in advance!

Dust

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

2 Replies
swuehl
MVP
MVP

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.

Not applicable
Author

Thank you very much - swuehl!

Your solution works like a charm.

Wish you all the best!