2 Replies Latest reply on Sep 27, 2016 12:44 AM by Abhas Jadhav

    Conditionally Disply sum and Average

    Abhas Jadhav

      Hi,

      I have to create a report where i am having dimension column as Type and Measures are Jan,Jun,............Dec. My report looks like below

       

      TypeJan
      Feb
      Mar
      Starts152530
      left102010
      present5520
      Expected101225

       

       

      In above form, I am getting all required output in SP. I have done calculations for present in SP only. Now i want to display data as SUM for where Type = Starts and lefts.

      Need to display AVG for Present

      And again want to display SUM for Expected.

       

      Is it possible? The sequence for type should be as above only.

       

       

      Thanks for support.

      Abhas

        • 1. Re: Conditionally Disply sum and Average
          Joe Oppelt

          IF ATTR([Type]) = 'present' then

          AVG([ ... whatever your measure field is ...]) ELSE

          SUM([ ... whatever your measure field is ...]) END

           

          You don't make it clear whether you have a single measure broken down by a MONTHS dimension, or if you actually have separate measures called JAN, FEB, MAR.  If the latter that's messy and you should break it apart by an actual date dimension.  (Otherwise, what do you plan to do when a 4th and a 5th month get added in?)

           

          But If it's the latter, then instead of AVG(...), you will do:

           

          ([JAN]+[FEB]+[MAR]) / 3

           

          And instead of SUM( ...) you will do:

           

           

          [JAN]+[FEB]+[MAR]

          • 2. Re: Conditionally Disply sum and Average
            Abhas Jadhav

            Thanks Joe,

             

            Appreciate your quick and correct reply. Its working for me.

             

             

            Thanks,

            Abhas