Group Values between dates

[Anonymous]

Hi all,

I'm trying to create a bar chart to display total Commission by month.  A sample of the data I have is as follows:

Contracts:

LOAD * INLINE [ContractId, Start, End,Commission

1, 01/05/2014,30/04/2015,40

2, 01/04/2014,31/03/2015,51

3, 01/10/2015,30/09/2016,38

4, 01/11/2013,31/10/2014,18

];

I'd prefer to do this at script level and come up with a table with columns as follows:

Month     Commission    

Jan-14     18

Feb-14     18

Mar-14     18

Apr-14      69

May-14     109

Jun-14      109

etc.

Possibly with ContractId included?

Any and all responses much appreciated, thank you!

[Anonymous · Accepted solution]

Hi all,

I've done some googling and I've managed to solve the problem with the help of a very useful blog post here: Loops in the Script

Here's my solution for anyone else that needs it:

Load  ContractId,

         addmonths(Start,IterNo() - 1) AS CommissionMonth,

         Commission

Resident Contracts

While addmonths(Start,IterNo() - 1) <= Contract_EndDate;

[#3]

First expand the start to End to one date for each row and apply the aggregation on that

Try like below:

Contracts:

LOAD

  *,

  MonthStart(DayIn) AS MonthStartDate

;

LOAD

  *,

  Start + IterNo() -1 AS DayIn

While IterNo() <= (End - Start) + 1

;

LOAD 

  *

INLINE

[ContractId, Start, End,Commission

1, 01/05/2014,30/04/2015,40

2, 01/04/2014,31/03/2015,51

3, 01/10/2015,30/09/2016,38

4, 01/11/2013,31/10/2014,18

];

Final_Contracts:

LOAD

  ContractId,

  MonthStartDate,

  Sum(Commission) AS Commission // Apply your own transformation

Resident

  Contracts

Group By

  ContractId,

  MonthStartDate

;

Make sure that your DateFormat = 'DD/MM/YYYY' ;