Showing all values for a dimension but limit to my selection
Hello my fellow Qlikers, I'm hoping you can help me with a problem I've been struggling with all afternoon!
My graph has the following dimensions:
Articleno, stockno, sum(amountsold), sum(revenue) and sum(amountonstock)
Now I want the graph to show all stocknumbers under each articlenumber, even if so sales are made, the amount of stocknumbers per article is around 10, but the total amount of stocknumbers are 100+ and I don't need to show all of them for each article.
When I select "show all values" and deselect "suppress zero values" every article gets ALL stocknumbers, even ones that aren't connected to the article in dimension 1. If article 1 has stocknumbers 1-10, "when I do show all values" all stocknumbers (1-100) are shown for each article, but if I don't use "show all values", stocknumbers that have "0" in amount won't show up in the chart.
Is there any way to get all relevant stocknumbers, perhaps using set analysis?
You are correct, Show All Values on the Dimension tab will give you all stocknos, which is not what you want. Uncheck that.
Suppress zero values will hide records with zero amounts. You're saying with that checked, you're not getting stocknos with zero values? You may need to also uncheck Suppress Missing on that same tab. Suppress Missing will supress a record when there are no values at all. Similar to Suppress Zero, but different. Try unchecking both Suppress Zero and Suppress Missing.
Maybe it's a problem with QlikView or the data model and they might work in this case, but in most cases, I've given up hope on changing the suppress missing and suppress zero to make reports that are more or less "fixed". Any insight?
I guess I haven't noticed a lot of issues with those options. I usually guess and check my way through it until I get the results I want. That could be related to my datasets though, most of my data comes straight out of a data warehouse, so there aren't a lot of difficult joins needed.
One place where I definitely ran into the problem was using calculated dimensions. I had a very complicated example and those check boxes did nothing . I eventually had to change my method. I guess that makes sense though as those two checkboxes are expression related, not dimension.
All that being said, I don't need to show missing or zero values very often in my charts, so it may be that I haven't come across situations where they don't work as intended.
In that example, I got the same amount of records (with no selections) when I did the Only({1} Field) as when I just used Field. When making selections, the numbers would change.
If you're saying that the Suppress checkboxes don't work when selections are made, then I see that happening in the example. When a selection is made, it also filters on the dimension, which is probably why those Suppress checkboxes don't work in that case. Your suggestion works great when you want to show all dimensions regardless of selection and I don't think there is any way to do it without using that Set Analysis.
When I tried this and it works until I drill down, if I choose to only show 1 article number, all article numbers in the whole dataset are shown anyways, practically ignoring any selection I make in the sales table.
In my stock table I have articlenumber, stocknumber and the amount on that stocknumber.
I link all my sales with a key consisting of articlenumber and stocknumber.
if my sales selection doesn't have all the stocknumbers in the stock table, they don't show up in the graph (natually, they are skipped by the selection)
I've tried for several hours clicking the different options (show null values, show all values etc.) but none seems to work.
I've seen solutions using P(), but would I have to include all the fields in the sales table, and would it even work?
