Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;
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' ;