7 Replies Latest reply: Apr 22, 2016 8:21 AM by Sunny Talwar RSS

    How to extract dimensions for a specific value of a measure

    Derek Cook

      Hello! I am new to Qlik Sense and this is my first post. Despite all the great resources here on the Qlik Community - from which I have self-taught a lot about the tool in a few weeks! - I am stuck. Hopefully, someone here can help.

       

      In a nutshell, I am trying to identify the dimensions associated with a specific value of a measure.

       

      I have built an aggregation that creates a "Worst Performers" list based on several conditions for several metrics. No issue. I am greatly simplifying this since the expression is pretty complicated for some of the metrics (or so it seems to me!) so it is not syntactically exact.

       

      AGGR(

          if($(mSalesCYTD)>1000,

                if(GrossMarginChangeYoY < 0,

                    if(SalesGrowth < 0,

                          $(mSalesCYTD) * GrossMarginChangeYoY

                    )

                )

          )

          [Product Category], [Product SubCategory], [Product Group]

      )


      Next, I wrapped, the above expression in a MAX() function so that I can pull out the top value, second from top value, etc. Everything to this point works fine.

       

      Where I am stuck is that I want to take whatever value I get from the MAX() function and extract the product hierarchy dimensions - [Product Category], [Product SubCategory], [Product Group] - for the entry where the above expression equals the MAX() value.


      For example, say the aggregation looks like this:


      Product Category    Product SubCategory    Product Group    Gross Profit Change

      Widgets                    Brass                            Small                   -$195

      Widgets                    Brass                            Large                   -$196

      Widgets                    Steel                             Medium                -$145

      Widget Parts             Iron                               Extra Large           -$95


      Once I use MAX() to find the most negative value - -$196 - how can I then extract the following from the table:


      Product Category = Widgets

      Product SubCategory = Brass

      Product Group = Large


      In my data, the chances of two values being identical are almost zero, so this approach should theoretically yield a distinct value. However, if there is a better way to do, I am certainly open to ideas.


      Hopefully this is clear. Please let me know if I need to clarify.

       

      Thanks in advance for any assistance!