Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
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]
))
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?
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]
))
In addition FirstSortedValue() also allows you to get 1, 2, 3 values just like Min and Max
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)
Perfect! Thanks so much! Such an easy solution but I was completely stumped!
Awesome, I am glad we were able to get it resolved and learn something new
Best,
Sunny