Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

How to extract dimensions for a specific value of a measure

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!

1 Solution

Accepted Solutions
MVP
MVP

Re: How to extract dimensions for a specific value of a measure

By adding 2, 3 for second and third:

FirstSortedValue([Product Category],

Aggr(

    if($(mSalesCYTD)>1000,

          if(GrossMarginChangeYoY < 0,

              if(SalesGrowth < 0,

                    $(mSalesCYTD) * GrossMarginChangeYoY

              )

          )

    )

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

), 2)

7 Replies
MVP
MVP

Re: How to extract dimensions for a specific value of a measure

Try this:

1)

FirstSortedValue([Product Category],

-Aggr(

    if($(mSalesCYTD)>1000,

          if(GrossMarginChangeYoY < 0,

              if(SalesGrowth < 0,

                    $(mSalesCYTD) * GrossMarginChangeYoY

              )

          )

    )

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

))



2)

FirstSortedValue([Product SubCategory],

-Aggr(

    if($(mSalesCYTD)>1000,

          if(GrossMarginChangeYoY < 0,

              if(SalesGrowth < 0,

                    $(mSalesCYTD) * GrossMarginChangeYoY

              )

          )

    )

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

))



3)

FirstSortedValue([Product Group],

-Aggr(

    if($(mSalesCYTD)>1000,

          if(GrossMarginChangeYoY < 0,

              if(SalesGrowth < 0,

                    $(mSalesCYTD) * GrossMarginChangeYoY

              )

          )

    )

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

))

Not applicable

Re: How to extract dimensions for a specific value of a measure

That is just about what I need. From that I can pull out the dimensions for the worst case product. How can I modify those expressions to pull the second, third, etc. worst products?

MVP
MVP

Re: How to extract dimensions for a specific value of a measure

My bad, since you want the information for the lowest value, you need to use +Aggr() instead of -Aggr()

1)

FirstSortedValue([Product Category],

Aggr(

    if($(mSalesCYTD)>1000,

          if(GrossMarginChangeYoY < 0,

              if(SalesGrowth < 0,

                    $(mSalesCYTD) * GrossMarginChangeYoY

              )

          )

    )

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

))

MVP
MVP

Re: How to extract dimensions for a specific value of a measure

In addition FirstSortedValue() also allows you to get 1, 2, 3 values just like Min and Max

FirstSortedValue - chart function ‒ QlikView

MVP
MVP

Re: How to extract dimensions for a specific value of a measure

By adding 2, 3 for second and third:

FirstSortedValue([Product Category],

Aggr(

    if($(mSalesCYTD)>1000,

          if(GrossMarginChangeYoY < 0,

              if(SalesGrowth < 0,

                    $(mSalesCYTD) * GrossMarginChangeYoY

              )

          )

    )

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

), 2)

Not applicable

Re: How to extract dimensions for a specific value of a measure

Perfect! Thanks so much! Such an easy solution but I was completely stumped!

MVP
MVP

Re: How to extract dimensions for a specific value of a measure

Awesome, I am glad we were able to get it resolved and learn something new

Best,

Sunny