topic Aggregate data on monthly basis in QlikView

Aggregate data on monthly basis

david_ze — Mon, 17 Dec 2012 14:20:58 GMT

Hi All,

I'll appreciate your help on a issue I have.

I have a transactions data which contains the fields:

Date, Transation_Type, Amount

The first date is 01/01/2002.

I would like to aggregate the transctions to be like this:

We need to see the total amount at the end of each month for each Transaction_Type, i.e:

At the end of each month we should see the aggregated ammount of each Transaction_Type from 01/01/2002 (the 1st date).

I'll appreciate you prompt advise on how to do this.

Regards,

David

Re: Aggregate data on monthly basis

jagan — Mon, 17 Dec 2012 14:44:15 GMT

HI,

Try this

TableName:

LOAD

MonthName(Date) AS Month,

Year(Date) AS Year,

LOAD

Date,

Transation_Type,

Amount

FROM DataSource;

Now in your chart select Month and Transaction type as dimensions and Sum(Amount) as Expression.

Hope this helps you.

Regards,

Jagan.

Re: Aggregate data on monthly basis

swuehl — Mon, 17 Dec 2012 14:45:00 GMT

David,

you could first aggregate your Values grouped by Month and Transaction_Type, then aggregate the MonthlyValues per TransactionType (or the other way round, should end up the same):

INPUT:

LOAD Date(MakeDate(2011)+floor(RAND()*720)) as Date,

RAND()*10 as Value,

chr(65+floor(RAND()*3)) as Transaction_Type

AutoGenerate 250;

TMP:

LOAD MonthEnd(Date) as Month,

sum(Value) as MonthlyValue,

Transaction_Type

Resident INPUT

group by Transaction_Type, MonthEnd(Date);

drop table INPUT;

RESULT:

LOAD if(Peek(Transaction_Type)=Transaction_Type,

rangesum(peek(AggrMonthlyValue),MonthlyValue),

MonthlyValue) as AggrMonthlyValue,

Transaction_Type, Month

resident TMP order by Transaction_Type, Month asc;

drop table TMP;

Re: Aggregate data on monthly basis

david_ze — Tue, 18 Dec 2012 14:12:32 GMT

Thanks ! It works very well...