3 Replies Latest reply: Dec 18, 2012 9:12 AM by David Zenesh RSS

    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

        • Re: Aggregate data on monthly basis
          jagan mohan rao appala

          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
            Stefan Wühl

            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;