Showing all values for a dimension

[#1]

Hello All,

I am trying to create a report that shows all values for a dimension (including where a result is zero) but which also includes a higher level of the same dimension, which I am using to generate partial sums at that level. I have managed to make every value of the dimension display, using the 'Show all values' flag and deselecting the 'Suppress Zero Values' flag but now, every value of my lowest dimension appears for each value of the dimension above, regardless of whether that level is related or not. I have included a section of the table below to illustrate:

ProdLvl2	Core_Range	Sales
Bread	CONTINENTAL SAVOURIES	0
Bread	PARISIEN / BAGUETTE	22
Bread	DEMI / BATON	249
Bread	TRADITIONAL BREADS	27
Bread	DOUGHNUTS	0
Bread	POTATO PRODUCTS	0
Bread	CHICKEN PRODUCTS	0
Bread	PETIT PAIN / BOUCHON	0
Bread	MUFFINS	0
Bread	MORNING GOODS	0
Bread	MCNAMEES	23
Bread	BREAD RETAIL PACKS	0
Bread	POULTRY	0
Bread	HAMS	0
Bread	Total	320
Meats / Protein	CONTINENTAL SAVOURIES	0
Meats / Protein	PARISIEN / BAGUETTE	0
Meats / Protein	DEMI / BATON	0
Meats / Protein	TRADITIONAL BREADS	0
Meats / Protein	DOUGHNUTS	0
Meats / Protein	POTATO PRODUCTS	0
Meats / Protein	CHICKEN PRODUCTS	116
Meats / Protein	PETIT PAIN / BOUCHON	0
Meats / Protein	MUFFINS	0
Meats / Protein	MORNING GOODS	0
Meats / Protein	MCNAMEES	0
Meats / Protein	BREAD RETAIL PACKS	0
Meats / Protein	POULTRY	0
Meats / Protein	HAMS	0
Meats / Protein	Total	116

I am hoping that someone may know a way to restrict the values of 'Core Range' that display beneath ProdLvl2 to the values that are related to ProdLvl2, whilst still being able to show zero values for values of Core Range that belong to ProdLvl2. eg, Petit Pain does belong to bread but has zero sales so should be displayed beneath bread but not Meats/Protein , whilst Poultry has no sales but does not belong to bread so should only be displayed beneath Meats/Protein.

I have also tried using sets to limit the value of the dimension displayed but that gave the same result as above.

Many thanks in advance for any assistance.

[#2]

I think you need to get rid of the Show All Values on the Dimension. If you just uncheck Suppress Zero Values, you should get every Core_Range under the ProdLvl2 that it is associated with, even if the Sales are 0.

Show All Values is what is giving all values under each ProdLvl2.

[#3]

Thanks for the reply NMiller, but when I untick the Show all values box I lose the zero value results too. Does this work differently for anyone else?

[#4]

Yes, I used some test data similar to yours. Here's an attachment.

Sorry, the server keeps timing out and it won't attach my file. It takes my message edits though. I'll try to post it again later.

Okay, take III. I uploaded it to my personal files and here's the URL: http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.00.45.93/Sh...

Try that. It may only let me download it though.

[pablolabbe]

How are you loading this data?

These fields, ProdLvl2 and Core_Range, belong to a product hierarchy ? If so, try to create a table wich relates Core_Range to each ProdLvl2 and let the fact data only with Core_Range id.

I think this could work.

[#6]

Many thanks for the example NMiller. I can now see that the difference is that the chart can be configured to show zero values where they exist in the fact table but apparently can't be forced to show zero values instead of null, ie for dimension values where nothing exists in the fact table. I guess my solution is to force into the fact table a zero value record per customer/core_range combination but that will be a lot of records I need to create.

Is there a way that I can force the dimension to show a zero value where is is missing from the fact table? I have tried ticking the 'Populate Missing Cells' and unticking the 'Suppress Missing' but that doesnt seem to do the job.