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!
