Aggregate data on monthly basis

[david_ze]

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

[jagan]

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.

[swuehl · Accepted solution]

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]

Thanks !   It works very well...