Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Fq
Partner - Contributor III
Partner - Contributor III

Worst 15 Products in Calculated Dimension

Hi, I am using the following expression to calculate the Growth Rate of the Products in the last 60 days (Rolling).


(
Sum({<Date = {"$(='>=' & Date(Today() - 60, 'YYYY-MM-DD'))"}>} Sum(Units))
-
Sum({<Date = {"$(='>=' & Date(Today() - 120, 'YYYY-MM-DD') & '<=' & Date(Today() - 61, 'YYYY-MM-DD'))"}>} Sum(Units))
)
/
Sum({<Date = {"$(='>=' & Date(Today() - 120, 'YYYY-MM-DD') & '<=' & Date(Today() - 61, 'YYYY-MM-DD'))"}>} Sum(Units))

The thing is I need to create a calculated dimension with the worst 15 Products in terms of Growth Rate.

Why? Because I have more than 50,000 Products and I want to display in a Chart only a 15 of them, and the user with a button will be able to select top 15 or bottom 15. 

For Top 15 Products I created the following expression, which is working perfect:


Aggr(
if(rank((
Sum({<Date = {"$(='>=' & Date(Today() - 60, 'YYYY-MM-DD'))"}>} $(vUnits))
-
Sum({<Date = {"$(='>=' & Date(Today() - 120, 'YYYY-MM-DD') & '<=' & Date(Today() - 61, 'YYYY-MM-DD'))"}>} $(vUnits))
)
/
Sum({<Date = {"$(='>=' & Date(Today() - 120, 'YYYY-MM-DD') & '<=' & Date(Today() - 61, 'YYYY-MM-DD'))"}>} $(vUnits))
) <= 15, [Product Name]),
[Product Name]
)

 

However I am struggling a lot to create a calculated dimension for the bottom 15 Products. 

 

2 Replies
MartW
Partner - Specialist
Partner - Specialist

what kind of visualisation are you using?

if you are using a bar chart.

isn't it easier to just select the top/ or bottom x products (productname a dimension) and a simple $(vUnits) as a messure. maybe with some extra set analysis to filter on last 60 or last 30 days.

MartW_0-1686303210959.png

 

 

Fq
Partner - Contributor III
Partner - Contributor III
Author

Unfortunately it is not possible, because the idea is a toggle where you can select the Top 15 based on Growth, based on Sales, based on YoY%, Worst Products based on Growth, Worst Products based on Sales, etc. 

But the measure of the bar chart will be always the same, so that measure will apply to the top 15 based on the filter selected. And for some reason the bar chart doesn't let you change the expression you want to use to select the top N, it automatically use the one of the bar chart.