Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
david_ze
New Contributor III

Aggregate data on monthly basis

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Aggregate data on monthly basis

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;

3 Replies
MVP
MVP

Re: Aggregate data on monthly basis

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.

MVP
MVP

Re: Aggregate data on monthly basis

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;

david_ze
New Contributor III

Re: Aggregate data on monthly basis

Thanks !   It works very well...

Community Browser