6 Replies Latest reply: Jul 29, 2014 1:25 PM by Paul Sokolik RSS

    Conditional Aggr()

      Hi all,

       

      I'd like to use the Aggr() function with conditional dimensions. I'm creating a report where a user can dynamically select which dimensions they want to include in their export. Each time they add a dimension, the aggr() function needs to update to include that dimension (or the expression won't calculate correctly).

       

      The expression will always remain the same, for example, avg(SpendAmount). Let's say we have three dimensions to choose from - Dimension1, Dimension2, and Dimension3. If they user only selects Dimension1, only that dimension needs to be included in the Aggr() expression. If they choose all three, we need to include Dimension1, 2, and 3. I'm using an expression as follows:

       

      sum( Aggr(avg(SpendAmount),

                     if(SubStringCount(Concat(dimension_flag, '|'), 10), Dimension1),

                     if(SubStringCount(Concat(dimension_flag, '|'), 11), Dimension2),

                     if(SubStringCount(Concat(dimension_flag, '|'), 12), Dimension3));

       

      The SubStringCount(...) is just to figure out if the user has selected the dimension or not. I know that portion works correctly.

       

      When I select dimensions, the expression will not calculate. Is there any way to do what I want?

        • Re: Conditional Aggr()
          Manish Kachhia

          Create an Inline Table like below

          Load * INLINE

          [

               KPI

               Dimension1

               Dimension2

               Dimension3

          ];

           

          Create a List Box of KPI and use below expression

           

          SUM(Aggr(avg(SpendAmount),$(=GetFieldSelections(KPI))))

          • Re: Conditional Aggr()
            Manish Kachhia

            Consider you have below table

             

            Sales:

            Load * Inline

            [

              Country, Brand, Sales

              UK, A, 100

              UK, B, 120

              UK, C, 130

              GERMANY, B, 200

              GERMANY, C, 210

              POLAND, A, 190

              POLAND, B, 400

            ];

             

            Now Create an Inline table with your desired dimensions

             

            KPI:

            Load * Inline

            [

              KPI

              Brand

              Country

            ];

             

            Create a List Box with KPI as Dimension

             

            To find out the Max Sales per selected Dimensions....you can use below expression...

            =max(Aggr(SUM(Sales),$(=GetFieldSelections(KPI))))

              • Re: Conditional Aggr()

                Ahh I think this should work! Thank you! Let me test this solution and I'll come mark the thread.

                • Re: Conditional Aggr()

                  What if I want to show the user a "Clean" version of the KPI in their list box selector, but the dimensions included in the Aggr() function need to be the original field names? For instance:

                   

                  Load * Inline [

                  KPI, KPI_Clean

                  KPI1, Clean Dimension 1

                  KPI2, Clean Dimension 2

                  KPI3, Clean Dimension 3

                  ];

                   

                  If I put the KPI_Clean in the list box, GetFieldSelections() won't give me the original KPIs I need. Is there a way to allow them to select the clean names but return the original names to my aggr() statement?

                  • Re: Conditional Aggr()

                    Ok, here's my newest issue: I already have a table, DATA, which houses all of my load data from a source file and which contains the dimensions I'll want to use. I've created two separate Inline Load tables - DIMENSIONS and EXPRESSIONS, which I map to a unique key and then tell my custom table to show or hide based on the key, like below. If a user selects the "Spend (Check)" expression from the list box, I have an expression created in my custom table that says "if(A gets selected, show CheckSpend calculation)."

                     

                    Expressions:

                    LOAD * INLINE [metrics_, metric_flag

                      Spend (Check), A

                      Spend (Invoice), H

                      Spend (Firm Only), C

                      Spend (Client Only), D

                      Spend (Capital Only), F

                      % Billable, B

                      # Payees, E

                    ];

                     

                    Since both my DIMENSION and EXPRESSION table are unlinked/isolated tables, I am unable to use any variation of GetFieldSelections() or Concat() in my Aggr() statement. GetFieldSelections() only adds the data structure from my unlinked DIMENSION table, and even though that table contains fields with the same name as those of my data table, the aggr() function (I think) is trying to access data associated with the orphan DIMENSION table. Can I get around this in some way? Does that make sense?