topic Re: Group Values between dates in QlikView

Group Values between dates

Anonymous — Wed, 03 Feb 2016 12:29:36 GMT

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!

Re: Group Values between dates

Anonymous — Fri, 05 Feb 2016 11:13:17 GMT

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;

Re: Group Values between dates

Fri, 05 Feb 2016 16:50:32 GMT

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' ;