2 Replies Latest reply: Jul 21, 2013 9:20 AM by Steve Zagzebski RSS

    Aggregation if statment

    Steve Zagzebski

      I am struggling to write the proper expression to evalute at a product level how much of my last years income was retained (defined by having at least $1 in Current Year) and how much was lost. The problem is I need to summarize at a Rollup Product Level so I need aggregation (I think).

       

       

      Here is my sample data:

       

      Rollup Product    Product   :Last Year Rev   Current Year Revenue

      1                         a               100                    200

      1                         b               200                    0

      2                         b               300                    500

      2                         c               400                    800

      3                         a               500                    0

      3                         b               600                    1100

       

      What I want my object to look like:

       

      Rollup Product  Last Year Rev (Lost)   Last Year Revenue (Retained)

      1                              200                              100

      2                                0                                700

      3                               500                              600

        • Re: Aggregation if statment
          Gysbert Wassenaar

          You can calculate this in the script (recommended):

           

          T1:

          LOAD *

              ,if([Last Year Rev]>[Current Year Revenue], [Last Year Rev]-[Current Year Revenue] ,0) as Lost

              ,if([Last Year Rev]<=[Current Year Revenue], [Last Year Rev] ,0) as Retained

          INLINE [

              Rollup Product, Product, Last Year Rev, Current Year Revenue

              1, a, 100, 200

              1, b, 200, 0

              2, b, 300, 500

              2, c, 400, 800

              3, a, 500, 0

              3, b, 600, 1100

          ];

           

          Or with expressions in a chart with Rollup Product as dimension:

          Lost: sum(if([Last Year Rev]>[Current Year Revenue], [Last Year Rev]-[Current Year Revenue]))

          Retained: sum(if([Last Year Rev]<=[Current Year Revenue], [Last Year Rev]))

            • Re: Aggregation if statment
              Steve Zagzebski

              The problem with the expression is the data has to be evaluated at the Product dimension first. If I do it at the Rollup Product dimension then all Revenue would go to the "Retained" column because at a Product Rollup level there is a least $1 in total across multiple products.  It would result in the following.:

               

              Rollup Product  Last Year Rev (Lost)   Last Year Revenue (Retained)

              1                                0                              300

              2                                0                              700

              3                                0                             1100