7 Replies Latest reply on Nov 30, 2018 8:04 AM by Barry OConnor

    How to use Aggregation in a case statement without losing the grain

    Amar Amar

      Hi,

      I came across one situation and I'm struck how to do this.

      Please find the attached workbook and please advice how to do this fix.

       

       

      In the report , in the data test tab, I did Sales_profit calculation which is at the aggregation level sum([Sales])/sum([Profit]) , when i use this in a calc ' case aggregation Error' it is erroring out. I tried using LOD's to change the aggregation in a separate calculation 'case aggregation Using Fixed '  there is no error , but i'm doing something wrong. If you see the data in the second tab , it is wierd. Don't understand why it is showing some big numbers.

       

      Please advice how to fix this one.

      Thx

        • 1. Re: How to use Aggregation in a case statement without losing the grain
          Barry OConnor

          Amar,

           

          Change your calculation to include ATTR for the Segment dimension:

           

          case ATTR([Segment])

          when 'Consumer' then [Sales_Profit]

          end

           

          Since Sales_Profit is an aggregate, you need to aggregate the dimension. 

          • 2. Re: How to use Aggregation in a case statement without losing the grain
            Amar Amar

            Hi Barry,

            This helped. Thank you!

             

            My apologies,  I did not do the mock up correctly before . Please find the attached workbook.

            The case I'm trying is little different. With a parameter and calling measures, when i do that I have some aggregates and non- aggregates as in the Calc I've in the workbook ...'Select Segment Type'

            It is throwing me cannot mix aggregates and non aggregates. I tried to put sum(sales) and just sales ...but my use case is this. Is there a way that I can make the select segment type work calc work correctly.

             

            Please advice.

            I've attached the workbook again. Please let me know.

            • 3. Re: How to use Aggregation in a case statement without losing the grain
              Barry OConnor

              First, to fix the "Select Segment Type", you were close.  You can either add "sum" to the Sales or make both "Aggregate Sales".  Either would work:

               

               

              Are you trying to make the parameter select the segment you want displayed?  If that is the case, you can add a Segment Filter:

              and place that into the filters on the worksheet.  Then when you select the parameter, only the appropriate segment will show, with the appropriate calculation.

               

              Hope this helps!

              • 4. Re: How to use Aggregation in a case statement without losing the grain
                Amar Amar

                Hi Barry,

                My use case is ..I need to use both aggregate and non-aggregates in the above case statement same time. I just put profit there two times just to explain my use case.

                 

                I need to have :

                sum(sales) and just sales in the same case. Is there a way that i can aggregate Just the sales even before bringing into the case statement or do something in the case other than doing (Profit). I'm trying to use both sum(sales) and sales (some how doing this without sum without losing the grain) same time.

                 

                Thx

                • 5. Re: How to use Aggregation in a case statement without losing the grain
                  Barry OConnor

                  I'm a little confused with this.  Why are you trying to do a sum and the granular sales within the same calculation?  How will the data be displayed? 

                  • 6. Re: How to use Aggregation in a case statement without losing the grain
                    Amar Amar

                    sorry for the confusion..

                    I'm not trying to show sales twice.. that is just for an example why my case is failing..It can any other metric...Is there a way that I can change that aggregation to Non- Aggregation  , parse the case statement and show the data?

                     

                    In my actual use case..I've many measures using in the case.  of which some are aggregates and some are non- aggregates. I have a/b and some are sum(a)/sum(b) etc. ... I need to find a way to parse them all and control the measure being passed into worksheet using a parameter( and which is done aleady).Since i'm getting can't mix agg and non- agg ..that is where I'm struck

                    Not sure if i'm making sense?

                    • 7. Re: How to use Aggregation in a case statement without losing the grain
                      Barry OConnor

                      It appears what you are trying to do is to sum Sales when you select "Home Office", sales/profit ratio when selecting "Consumer" and the granular (non-aggregate) Sales when selecting "Corporate".  When you are saying the granular level non-aggregates, what dimensions will be used to get to this granular level? 

                       

                      For example (maybe this is similar to what you are trying to accomplish), let's say that you want to have Category as the dimension when "Corporate" is selected.  The report would then look like this:

                       

                      Sales is at the granular level of Category.  So, to them make the report to change the level of granularity, you make the dimension dynamic.  This can be done in a calculation based on the parameter:

                       

                      Then change your "Select Segment Type" to have them all aggregates (the Dynamic Dimension will determine the level of granularity).

                      Then the parameter will drive the selections: