Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I have the following data (it's just a random data set. I can't show the original one due to privacy reasons).
Country Item 1 Item 2 Item 3 Item 4
Spain 400 500 700 500
France 650 800 900 200
France 150 300 400 800
Item 1, Item 2, Item 3 and Item 4 represent the number of units sold for each item. I need display two different stacked bars for each country working with Qlik Sense: the first one will represent Item 1 and Item 2 sales and the second one Item 3 and Item 4 sales. Is it possible to achieve it? Is there any extension might help me solve this issue?
I would really appreciate if anyone could help me solve this issue.
Best regards,
Gonzalo
Hi Gonzalo,
I hope that I understood your request correctly. This can be solved, but it will require a data island and a calculated dimension, which can be quite heavy on performance in case your data set is large (millions of rows).
1. You will have to expand the country dimension to two lines (groups for items 1+2 and 3+4). Then add a second dimension that will be used for stacking.
The easiest way how to do this is by creating a data island in the script, e.g.:
stacked:
load * inline [
group, item
A, 1
A, 2
B, 3
B, 4
];
2. Add the first dimension to the bar chart as a calculated one:
=[Country] &' - '& [group]
3. Add the second dimension to the bar chart = item (from the data island)
4. Add the conditional expression that will sum the correct field based on the "item" field value:
=pick([item],sum([Item 1]),sum([Item 2]),sum([Item 3]),sum([Item 4]))
When you color the chart by the "item" dimension, it should look something like the attached picture.
Hope this helps. Regards,
Radovan
I would like to highlight that having two different graphs (first stacked bar vs Country and second stacked bar vs Country) would not solve the problem. Both stacked bars must be side-by-side.
Thank you very much.
Gonzalo
You need a stack bar with 1 dimension?
Actually there should be two stacked bars for each country (one dimension). One of them would represent sales for Item1 and Item2 and the other one sales for Item3 and Item4.
This extensions has more than 1 dim and more than 1 measure for Stack back. Check the Chart Editor after you add Dim/Measures
This is a very interesting extension but I think it is not useful for this situation.
Thank you very much.
Best regards,
Gonzalo
Hi Gonzalo,
I hope that I understood your request correctly. This can be solved, but it will require a data island and a calculated dimension, which can be quite heavy on performance in case your data set is large (millions of rows).
1. You will have to expand the country dimension to two lines (groups for items 1+2 and 3+4). Then add a second dimension that will be used for stacking.
The easiest way how to do this is by creating a data island in the script, e.g.:
stacked:
load * inline [
group, item
A, 1
A, 2
B, 3
B, 4
];
2. Add the first dimension to the bar chart as a calculated one:
=[Country] &' - '& [group]
3. Add the second dimension to the bar chart = item (from the data island)
4. Add the conditional expression that will sum the correct field based on the "item" field value:
=pick([item],sum([Item 1]),sum([Item 2]),sum([Item 3]),sum([Item 4]))
When you color the chart by the "item" dimension, it should look something like the attached picture.
Hope this helps. Regards,
Radovan
Hi Radovan,
Thank you very much for your reply! That is what I needed and works great. Perfect
Best regards,
Gonzalo