topic Re: Showing gaps in data in the graph in QlikView

Showing gaps in data in the graph

Anonymous — Tue, 11 Jun 2013 09:18:09 GMT

Hello!

I have a problem regarding the visualization of sales data.

We have ArticleNo, Date, Quantity. Not all articles sell on all dates and thus won't have an entry in that table. And we have a MasterCalendar, which contains all dates, including those where nothing is sold.

I'd now like to create a bar chart along the date and article dimensions, which shows sales per date (sum(Quantity)). But I'd like to leave a gap where no sales of the article was made. At the same time I want to honour the time selections, e.g. sales for April. for that reason the "show all values" of the date dimension doesn't seem to be the solution.

I have tried all conceivable combinations of "Show all values", "Ignore null values" (for dimensions) and "Ignore null values" and "Ignore missing values" (for the presentation). I have also tried various combinations of set analysis including the attempt to create a dynamic dimension, which specifically included all dates that I want and no more

e.g. aggr(only({1<Year=$::Year,Month=$::Month,Day=$::Day>} Date), Date)

by which I then hoped to be able to use the "Show all values"option

But all to no avail. I am about to conclude that I have to pump up the sales data with zeros for all article-date-combinations, which don't contain sales. Is what we need not possible or am I missing something here?

Thank you very much!

Sandro

Re: Showing gaps in data in the graph

Tue, 11 Jun 2013 09:35:53 GMT

I thought the solution would be to use a continious dimension in the axis tab

but unfortunately when I tested it, the bars of the different articles get one behind another.

So this solution seems to work only if you use a 3D-style bar chart.

Unless anyone knows how to achieve that differently ?

Re: Showing gaps in data in the graph

Anonymous — Tue, 11 Jun 2013 12:10:05 GMT

Thank you for the suggestion! 🙂

That partly works:

- For one, as you also noticed, only with a line or 3D bar chart it seems. Then all values are visible

- The other thing is (and I hadn't mentioned that) that when I switch to another, non-numerical time dimension (like 'year-week', '2013-12') then no continuous axis is possible

Sandro

Re: Showing gaps in data in the graph

Tue, 11 Jun 2013 12:39:30 GMT

Well it depends on what are your other dimensions

If it is only things like year-week, you can create another field like yearweek, then 201312, 201401 ... will be numerical values

Re: Showing gaps in data in the graph

Tue, 11 Jun 2013 12:43:56 GMT

(but I just realized you will have a huge gap between 201352 and 201401).

Maybe the trick of initializing every date/article with a 0 will be the less painful way to do.

Re: Showing gaps in data in the graph

Anonymous — Tue, 11 Jun 2013 13:06:03 GMT

I think you are right in both cases. Even though making a compromise on the formatting I might convert the time elements to numbers. The disadvantage you mentioned in your second post, would either lead to compromising the linearity I am after in the first place or lead to even less readable dimension items (e.g. _MonthSerial)

Thank you for your efforts 🙂