Top 10 stacked bar chart with one or two dimensions

[#1]

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.

[JonnyPoole]

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).

[#3]

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>

[#4]

Thank you - I understand I need to use sets, but I can't get it to work, the measure ends up empty.

1. I have data for 2008 through 2014. 
2. I want to show a stacked bar for each year.
3. I want to find the top 10 commodities for one of the years, say 2014 and show those 10 commodities for each year with the measure being the total spend for each commodity in each year.
4. Since the company has changed some commodity codes over the time period, there will be zero spend for a couple of the commodity codes in the first 2-3 years.
5. Do I use the set in the commodity dimension to control the commodities for all years or does the set go in the measure?  Right now it shows all commodities in a huge stacked bar for each year
6. I'm using Dim 1 = Year, Dim 2 = Commodity, measure = Sum({1<"[year] = 2014,[Commodity] = {rank(total sum([cost]))<=10">} [cost]).

Thanks for your help.

[JonnyPoole]

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.