4 Replies Latest reply: Jan 26, 2018 12:51 PM by vidya sagar malla RSS

    Multiple Dimension aggregation Columns

    vidya sagar malla

      Hello Everyone,

       

      From the below sample data table. I want to create an aggregational table and later I will convert it to the scatter plot which shows aggregation of the Products based on their sales amount and Actual amounts. I considered using the ranking for this approach but I am not getting the results as expected.

       

      sample data table:

          

      ProductSales AmountActual AmountRisk ValueReturn Value
      A6052015
      B501002525
      C70705535
      D20351530
      E10251715
      F5302020

       

      Expected Result:

        

      DimensionRisk ValueReturn Value
      Average of TOP 3 Products by Sales Amount33.3325
      Average of TOP 3 Products by Actual Amount31.6630

       

      Summary:

       

      Average of TOP 3 Products by Sales Amounts: C,A,B are the Top 3 Products. Now I want Avg(Risk Value) and Avg(Return Value) of these Products.

       

      Average of TOP 3 Products by Actual Amounts: B, C, D are the Top 3 Products. Now I want Avg(Risk Value) and Avg(Return Value) of these Products.


      Expressions I am using:


      Dimension Expression:


      if(aggr(rank(sum( [Sales Amount])),[Product ID])<= 3,'Average of TOP 3 Products by Sales Amounts',

      if(Aggr(rank(-sum( [Actual Amount])),[Product ID])<= 3 ,'Average of TOP 3 Products by Actual Amounts'))

       

      Measured Expression:

       

      Avg(Risk Value)

      Avg(Return Value)

       

      Thanks in Advance,

      -Vidya

        • Re: Multiple Dimension aggregation Columns
          omar bensalem

          So basically:

           

          to have the first 3 products by sales amout:

          Sum({<Product={"=rank(sum([Sales Amount]),Product)<=3"}>}[Sales Amount])

          Capture.PNG

          to have the first 3 products by actual amount:

          Sum({<Product={"=rank(sum([Actual Amount]),Product)<=3"}>}[Actual Amount])

          Capture.PNG

           

          so :

          to have the avg of risk value depends on the top 3 based on sales amount:

          Avg({<Product={"=rank(sum([Sales Amount]),Product)<=3"}>}[Risk Value])


          Capture.PNG

           

          to have the avg of risk value depends on the top 3 based on actual amount:

          Avg({<Product={"=rank(sum([Actual Amount]),Product)<=3"}>}[Risk Value])

          Capture.PNG

           

          if u want to have this in a table:

          as dimension:

           

          =ValueList('Average of TOP 3 Products by Sales Amounts','Average of TOP 3 Products by Actual Amounts')

          as a measure:

           

          if(ValueList('Average of TOP 3 Products by Sales Amounts','Average of TOP 3 Products by Actual Amounts') =

          'Average of TOP 3 Products by Sales Amounts', Avg({<Product={"=rank(sum([Sales Amount]),Product)<=3"}>}[Risk Value]),

          if(ValueList('Average of TOP 3 Products by Sales Amounts','Average of TOP 3 Products by Actual Amounts') =

          'Average of TOP 3 Products by Actual Amounts',Avg({<Product={"=rank(sum([Actual Amount]),Product)<=3"}>}[Risk Value])

          ))

           

          result:

          Capture.PNG

            • Re: Multiple Dimension aggregation Columns
              vidya sagar malla

              Hi Omar,

               

              You are really a genius. I really appreciate your help today. The expression you shared worked as expected. One last thing i would like to share is. I also want to include my own products example: If(wildmatch([Product],'*Micro*'),[Product]). Is there a way that I can add this if condition to the valuelist?

               

              Thanks,

              -Vidya

                • Re: Multiple Dimension aggregation Columns
                  omar bensalem

                  If I fully understand ur request:


                  Let's say you have 1000 products, but only 50 that contains Micro, and u want to to do the Avg for the top 3 of these 50?

                  Is that so?

                   

                  If yes, u change ur expression from:

                  Avg({<Product={"=rank(sum([Sales Amount]),Product)<=3"}>}[Risk Value])


                  to

                  Avg({<Product={"=rank(sum(  {<Product={=wildmatch([Product],'*Micro*')=1}>}  [Sales Amount]),Product)<=3"}>}[Risk Value])


                  so: the final expressions would be:

                   

                  as dimension:

                   

                  =ValueList('Average of TOP 3 Products by Sales Amounts','Average of TOP 3 Products by Actual Amounts')


                  as a measure:

                   

                  if(ValueList('Average of TOP 3 Products by Sales Amounts','Average of TOP 3 Products by Actual Amounts') =

                  'Average of TOP 3 Products by Sales Amounts',

                  Avg({<Product={"=rank(sum({<Product={=wildmatch([Product],'*Micro*')=1}>} [Sales Amount]),Product)<=3"}>}[Risk Value]),

                  if(ValueList('Average of TOP 3 Products by Sales Amounts','Average of TOP 3 Products by Actual Amounts') =

                  'Average of TOP 3 Products by Actual Amounts',

                  Avg({<Product={"=rank(sum({<Product={=wildmatch([Product],'*Micro*')=1}>} [Actual Amount]),Product)<=3"}>}[Risk Value])

                  ))

                    • Re: Multiple Dimension aggregation Columns
                      vidya sagar malla

                      Hi Omar,


                      I found the solution for this. let's follow the order.

                       

                      • I have 1000 Products, But only 50 that contains Micro. Now I want to show Avg risk value of top 3 by sales amount of 1000 Products and individual Riks values of products that contains Micro.

                      Example:

                       

                      Capture.PNG

                            Micro1                                                                                                                    10.20

                            Mirco2                                                                                                                    12.36

                       

                       

                      • Solution to achieve this requirement is.

                       

                      load * inline [

                      Dim

                      1

                      2

                      3

                      4

                      5

                      6

                      7

                      8

                      9

                      10

                      ];

                       

                      Dimension:

                      =pick(Dim,'Average of TOP N Products by AUM: 1 YR Risk and Returns','Average of TOP N Products by 1 YR Flows: 1 YR Risk and Returns','Average of Bottom N Products by 1 YR Flows: 1 YR Risk and Returns',

                      if(WildMatch([Firm Name],'*Mirco*'),[Product Name]))

                       

                      Measure:

                       

                      =

                      PICK(Dim, Avg({<[Product ID]={"=rank(sum([Sales Amount]),[Product ID])<=10"}>}[Risk Value]),

                                Avg({<[Product ID]={"=rank(sum([Total Amount]),[Product ID])<=10"}>}[Risk Value]),

                                Avg({<[Product ID]={"=rank(-sum([Total Amount]),[Product ID])<=10"}>}[Risk Value]),

                      ([Risk Value])

                      )

                       

                      Thanks,

                      -Vidya