Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hopefully this is simple and I am just missing something. I am new to using Qlik Sense.
I have purchasing data with year, commodity and vendor. I want to be able to create 2 charts as follows.
Chart #1
Dimension 1 = year, Dim 2 = Commodity. Measure = sum of spend for each commodity. I want one stacked bar for each year showing the top 10 commodity spend amounts. I can do this with a regular bar chart, but when I change to a stacked bar, the limitation capability disappears and the stacked bar shows ALL commodities for each year. Is this because the Top 10 are not the same for each year? If so, why does it work in the regular grouped bar chart which can show a different Top 10 for each year?
Chart #2 - same concept really, but with a filter
Dim 1 = Year, Dim 2 = Vendor, Measure = Spend by vendor. Filter = Commodity
I want to filter for one commodity and show the Top 10 vendor spend sums for each year.
Also - is it possible to have only one sheet filter for the single commodity, not all sheets in the app?
Thanks in advance and if you are in the US - Happy Thanksgiving.
You can do that. The trick is to use SET ANALYSIS in your measure expression to focus the data set to the top 10.
If your measure is say, sum(Sales)
Use the following SET Statement to focus it to the 10 countries with the most sales
Sum( {$<Country={"=rank( total sum(Sales))<=10"}>} [Sales] )
... in this statement, '$' means that the set of data will respond to all user filters . The '<Country={"=rank( total sum(Sales))<=10"}>' means that Country (no matter if a user selects a country filter in the dashboard) will always be the top 10 countries by sum(Sales).
You can use this for #2 .
Your last question is not really possible right now, although you can use the above technique to a point to do this anywhere you use an aggregation function . ( SET ANALYSIS is valid within the context of a chart aggregation).
Thank you so much, I'll give this a try later today.
Jay Hole
216-470-4472
On Wed, Nov 26, 2014 at 12:59 PM, Jonathan Poole <qcwebmaster@qlikview.com>
Thank you - I understand I need to use sets, but I can't get it to work, the measure ends up empty.
Thanks for your help.
Here is an example that evaluates the top countries in the Year 2005 on the right side (11 of them, Germany at the bottom) , takes the top 10 and shows their values by Year on the left side
The measure expression is as follows:
Sum( {$<Country= p( {1<Year={2005},Country={"=rank( total sum( {1<Year={2005}>}Sales))<=10"}>} )>} [Sales] )
Here i am using the p() function to gather a subset of countries (top 10 in Year=2005 regardless of other user filters) , and always plot those same 10 customers by year (and filter the Sales number by user selection)
There are a few things going on here to ensure i'm grabbing the right context at each level of the expression so let me know if you need further explanation.