show all values within group or subgroup?

[#1]

Hi,

I have a bar chart, in which I am showing categories on x axis and sale on y axis. The customer wanted that I show all categories on the graph whether they had zero sale or positive sale. I clicked on show all values and then in presentation unchecked supress zero value and supress missing values. It worked like a charm and everything looked great

Then there came another scenario where I need to show drilldown from category to sub-category. Now the requirement was that If for example I click on cosmetic category to go one step down to sub-categories, Only the sub-categories of cosmetic should show. Show all the sub-categories of cosmetic whether they had sale or not.

Now the problem with show all values option is that it is also showing sub-categories from other categories. If I check off the show all value option, then only sub-categories of cosmetic appear but then it also removes the zero sale sub-categories of cosmetic category

I will really appreciate if someone could bail me out from this problem which has become a pain in the neck for me

Regards

Arif

[ChristofSchwarz]

Can you isolate the problem with a simple data model of a few data lines and attach as a qvw? This can help me find an answer.I think I understand what you want, and it might end up to add a few lines of "neutral" data in the script, so that the chart can differenciate between "missing" and "zero" values. If you opted "show missing values" then the filter on the parent category becomes obsolete - but there are ways to work around that.

[sureshbaabu]

Hello,

you dont have to select Show All Values for the dimesnion. if you uncheck - 'supress Zero Values' on expression that should be enough.

This will still show the dimenions even if it's value is null or Zero

Thanks

[DavidFoster1]

The simplest option is to fill in the gaps between product group/sub group and (I am guessing) date with zeroes. It bulks up your data (potentially a lot), but means that you don't need to worry about these issues on the front-end.

[#5]

Hi,

Thanks for the quick reply. Please find attached the test qv doc. Please remember that client does not just want to show zero or null data, but he even wants to show those relevant categories and subcategories that do not have any record on the selected date.

The attachment example has categories and subcategories. If I select date 1/1/2013, the fruit category does not have any record for this date, but I still want to show this on the graph with zero value. The graph is doing correctly

Now If I select on cosmetic, then in the drilldown, I only want to show all the subcategories of cosmetic whether they have data for the selected date or not. But I do not need other subcategories

Arif

[DavidFoster1]

Here you go. I have used an IterNo approach to add zero sales for the 'missing' months.

[sudeepkm]

I understand that your requirement is little complex. I've a quick thought which can be implemented but that may not be the best practice.

The idea is to have two charts and display one another based on the Group value you selected.

Have one chart with Show All Value when Group value is CAT (GetCurrentField([New Group])='Cat')

Have another chart exactly placed at the same location with uncheck of Show All Value when Group value is SubCat (GetCurrentField([New Group])='SubCat')

It may not be the best solution but for urgent situation you can use it as workaround.

Please find the attachment.

[sureshbaabu]

we might have to transpose the data - Convert the values of Date column as column headers.

and this will fill in 'null' for the rows, where there is no value in the source.

I believe there can be easier solution's for this. I will wait to hear from others

[ChristofSchwarz]

Here is a way to avoid adding zero-sales rows to the data.

--> Just add a second expression to your chart, but deselect "Show as bar". he expression has the formula

SUM({1<Cat=P(Cat)>} Reven)