6 Replies Latest reply: Dec 18, 2013 5:01 AM by Israr Khan RSS

    Conditional Sum

    Naheem Ali

      Hi

       

      I need to create new fields giving me YTD figures for Budget.

      So say

      YTD1     80111.75

      YTD2     160223.5     (adding budget for period 1 & 2)

      YTD3     240335.25     (adding budget for periods 1,2 & 3)     

       

       

      Cost CentreAccountPeriodBudget
      12345953211180111.75
      12345953211280111.75
      12345953211380111.75
      12345953211480111.75
      12345953211580111.75
      12345953211680111.75
      12345953211780111.75
      12345953211880111.75
      12345953211980111.75
      123459532111080111.75
      123459532111180111.75
      123459532111280111.75

      Regards

        • Re: Conditional Sum
          Vamshi Tataipelli

          LOAD [Cost Center],

               Account,

               Period,

               Budget,

               Period * Budget as YTD

          FROM

          [Tablename];

          • Re: Conditional Sum
            Naheem Ali

            Thanks Vamshi

             

            Sorry I have should have mentioned that budget value per month can vary so cant do a simple times.

             

            Regards


              • Re: Conditional Sum
                sujeet singh

                please explain in details what you want

                • Re: Conditional Sum
                  Adam Booth

                  I think the RangeSum() and Peek() functions can help you here. First you need to load your data and then perform a resident load with the RangeSum() function calculating the cumulative YTD value. The Peek() function takes the value of the previous row for the selected field. Finally we drop the original data table.

                   

                  BaseData:

                  LOAD [Cost Centre],

                            Account,

                            Period,

                            Budget

                  From [Data Source]

                   

                  CalculatedData:

                  NoConcatenate

                  LOAD [Cost Centre],

                            Account,

                            Period,

                            Budget,

                            RangeSum(Budget, Peek('YTD')) as YTD

                  RESIDENT BaseData;

                  DROP TABLE BaseData;

                • Re: Conditional Sum
                  Naheem Ali

                  Hi Sujeet

                  I am trying to create a Budget cost centre report which has the following figures

                                                                                          Month                                                       YTD

                  Cost Centre Code     Annual Budget     Bugdet  Spend     Variance          Budget YTD     Spend YTD     Variance YTD

                   

                   

                  I am pulling the data from an Oracle database which doesnt have a YTD field.  The raw would look something like:

                  Period     Budget     Cost Centre     Account

                  1               1000         ******               *******
                  2               1200

                  3               1100

                  4                1300

                  Spend field would data in same format.

                   

                  Regards