grouped and stacked bar charts issues

[#1]

Hey all,

I try to realize a flexible grouped and stacked bar chart. The main aspect is to show data of different people in two dimensions. First dimension is the year and second a category. The data should be stacked and grouped at the same time. Until now I got all this done and it works quite fine, but I also would like to get some flexibility in changing the apperance of the bar chart by selecting combinations.

Following table is my data source.

year	category	Peter	Paul	Tobias
2014	A	20	12	6
2014	B	5	16	3
2015	A	8	10	11
2015	B	45	31	7
2016	A	0	8	13
2016	B	21	4	18

In the load script I simply loaded this table and created a combination of year and category.

data:
LOAD year,
      category,
      Peter,
      Paul,
      Tobias,
      year &'-'& category as combination
FROM
sample_data.xlsx
(ooxml, embedded labels, table is Tabble1);

The bar chart result works as defined:

But when I want to display data in a flexible way I stand in front of a big problem. How can I disable the display of the empty dimensions? The result just should display year 2014 with category A & B, year 2015 only with category B, and year 2016 only with categroy A. So the "empty" dimension should disappear.

I tried to write some if-functions in the dimensions area. So something like if(sum of data in this dimension is <= 0, 0, year). But it didn't work out ...

Thanks for reply!

BR

Henrik

[Gysbert_Wassenaar]

You can't if you want to use two dimensions for the x-axis. You can try using a calculated dimension that concatenates the Year and Category dimensions: = Year & ' - ' & Category.

[#3]

Thanks for your reply, but if I do  what you suggested I lose the "grouped" feature ... and I definetly need to display as groups. Isn't there any other possibility? I am also in the situation that I can change the structure of my data source, so the most important is that I can realize the shown bar format. Any ideas someone?

[#4]

HI,

You can simply go to chart properties and under the dimension tab just select the check box displaying 'Suppress when value is null '

Hope it works

[#5]

that was the first thing I tried, but unfortunately it doesn't work

[#6]

Does really no one see a solution for my problem? I am still struggeling with that. And again I am 100% flexible with the table structure!!! I just need to realise the diagram results.

Thanks for reply!

[Gysbert_Wassenaar]

You want the grouped feature, but you don't want it to act like the grouped feature. Well, that's simply not possible. If you use two dimensions on the same axis then space is reserved for each dimension value (A and B in your example) even if no data is available.