10 Replies Latest reply: Aug 23, 2018 1:04 AM by Darius Pranskus RSS

    Top N by Dimension using Set Analysis in Expression

    Steve Taylor

      I have a need to show top 25 products by Sub Department in my company.

       

      I've seen several descriptions and solutions to this problem, Oleg Troyansky has an excellent write-up on this topic where he shows solutions using Dimension Limiting in Straight tables, Calculated Dimensions in Straight/Pivot tables and expression limiting using set analysis in a Pivot table.

       

      If you haven't read this article, you should - it is an excellent read.

       

      However when he discusses he set analysis version he no longer breaks it out by dimension. Simply top 100 - or 1000 can't remember which overall.

       

      For my needs, assume we have 4 sub departments and I need to show top 25 products in each. This should yield 100 rows.

       

      For all my tinkering, I can only get it to show top 25 over the available set, broken down by sub department. That's 25 Total.

       

      I could probably solve the problem using a calculated dimension or two, but I'm stubborn and am looking for optimal performance. This is my expression (showing top 25 overall):

       

      sum({<MasterPLUCode={"=rank(sum(Amount*NetFlag),4)<=25"}>}Amount*NetFlag)

       

      Any help offered would be hugely appreciated.

        • Re: Top N by Dimension using Set Analysis in Expression
          Manish Kachhia

          Can you provide little data with sample solution in excel file?

          • Re: Top N by Dimension using Set Analysis in Expression
            Piet Hein van der Stigchel

            The Set Analysis in an expression is evaluated once for the whole chart, not for every dimension value seperately.

            • Re: Top N by Dimension using Set Analysis in Expression
              Darius Pranskus

              Hi

               

              You can try the following expression for Net Sales. In this case you don't need a dimension.

               

              SUM({<MasterPLUCode={"=AGGR(Rank(Sum(Amount*NetFlag),4), [Sub Department], MasterPLUCode)<=25"}>} Amount * NetFlag)

               

              Cheers

              Darius

                • Re: Top N by Dimension using Set Analysis in Expression
                  Steve Taylor

                  Darius,

                   

                  I tried this last night and it didn't work, but that was against my full document. When tried against the sample qvw I provided, it worked like a charm. There must be an issue in my data model/load that I need to track down - which is good to know.

                   

                  Awarding you for the answer as it was exactly what I was looking for. Just wish I'd tried it on the sample document before posting and saved myself a bunch of time

                   

                  Thanks again!

                    • Re: Top N by Dimension using Set Analysis in Expression
                      Steve Taylor

                      So I tracked down this problem. When I created the sample qvw, I only loaded our sales table, not the discounts or coupons tables.

                       

                      When the consultant first created this application for us, they converted the PLUs in the sales table to integers, but not in the other tables which get concatenated into facts. This meant that my masterplus were only being linked to sale items and not all fact items.

                       

                      This error has been in our application for over a year now and only found thanks to this exercise and the help of folks on this forum!

                    • Re: Top N by Dimension using Set Analysis in Expression
                      Chandni Bhowmick

                      Hi Darius,

                       

                      I tried applying your solution to my problem. I need to show the top 10 projects by region (Need to use expression, not calculated dimension)

                       

                      Expression1:

                       

                      Sum({<[Project Name]={"=AGGR(Rank(Sum({<FunnelStamp2={'Current'} ,SOPCatagory={'Pipeline','Committed','Upside'},SixQtrFunnelFlag={'Yes'},DataType={'6Q'},[Development Stage]={'Obsolete - Diminishing Inventory','Sunset - Pending Obsolescence', 'Intent to Cancel'}>}Amount),4),Region,[Project Name])<=10"}>}Amount)/1000000

                       

                      If I use the above expression then the projects are limited to 10 but the numbers aren't correct (image 1) since it's taking the total amount without considering the filters. However, if I use the expression below the numbers are correct but it's not limited to 10 projects any more (image 2). What am I doing wrong?

                       

                      Expression 2:

                      Sum({<[Project Name]={"=AGGR(Rank(Sum({<FunnelStamp2={'Current'} ,SOPCatagory={'Pipeline','Committed','Upside'},SixQtrFunnelFlag={'Yes'},DataType={'6Q'},[Development Stage]={'Obsolete - Diminishing Inventory','Sunset - Pending Obsolescence', 'Intent to Cancel'}>}Amount),1),Region,[Project Name])<=10"}>}{<FunnelStamp2={'Current'} ,SOPCatagory={'Pipeline','Committed','Upside'},SixQtrFunnelFlag={'Yes'},DataType={'6Q'},[Development Stage]={'Obsolete - Diminishing Inventory','Sunset - Pending Obsolescence', 'Intent to Cancel'}>}Amount)/1000000

                        • Re: Top N by Dimension using Set Analysis in Expression
                          Darius Pranskus

                          Not sure 100%, but it might not work, because you are using two sets with set analysis

                           

                          So the first expression limits only [Project Name], and if you want filters on other fields you need to repeat them once again. But do not close the set, but continue it

                           

                          Please try this one

                           

                          Sum({<[Project Name]={"=AGGR(Rank(Sum({<FunnelStamp2={'Current'} ,SOPCatagory={'Pipeline','Committed','Upside'},SixQtrFunnelFlag={'Yes'},DataType={'6Q'},[Development Stage]={'Obsolete - Diminishing Inventory','Sunset - Pending Obsolescence', 'Intent to Cancel'}>}Amount),1),Region,[Project Name])<=10"}, FunnelStamp2={'Current'} ,SOPCatagory={'Pipeline','Committed','Upside'},SixQtrFunnelFlag={'Yes'},DataType={'6Q'},[Development Stage]={'Obsolete - Diminishing Inventory','Sunset - Pending Obsolescence', 'Intent to Cancel'}>}Amount)/1000000


                          My suggestion would be to create a variable for the expression and then pass the list of values to it with $ sign expansion.