Force showing all values on x-axis

[#1]

Hi,

I have loaded two tables into qlik sense and linked these: one with a calendar that contains (among others) a column for the date and the year/month combination, and one with orders, containing columns for 'shipping date', 'customer', 'product' and 'quantity'.

Not every customer orders every product every month, and when I make a bar chart that groups sales by year/month on the x-axis, only the months in which there were sales are displayed (for example, if a certain customer only ordered a certain product in April and June of this year, and I filter down to this combination, it will remove all months except for these two from the x-axis, which does not look very good). I have tried switching on the 'show null values' option, but this does not work. I guess this is because in the source data these really aren't entries with null values, but rather no data exists at all.

My question is: is there any way to force the chart to conistently show all months on the x-axis (or more ideally, only all months from the first to the last month in which there were sales), regardless of the customer and product I select, and regardless of whether any data exists for that month? In other words, all non existing data should simply show up as months with 0 sales.

I have found some other threads on the forum asking for essentially the same thing (for example here), but unfortunately I have not been able to apply the solutions offered in these to my problem with any success.

Thanks for the help!

[petter]

You can make a so-called Master Calendar that contains all the dates from min to max date. You generate this in your load script and it is a standard thing that is taught in QlikView classes and the subject of most QlikView books. QlikView Components even have a best-practice "component"/script-function to generate one for you:

https://community.qlik.com/thread/40043

There is a lot of information on this community if you search for Master Calendar - for instance this intro from Michael Tarallo:

Understanding the Master Calendar (video)

[petter]

This piece of code that I snipped from a community post (response from Marco Wedel) demonstrate the basics of it:

tabCalendar:

LOAD *,

     Day(Date) as Day,

     WeekDay(Date) as WeekDay,

     Week(Date) as Week,

     WeekName(Date) as WeekName,

     Month(Date) as Month,

     MonthName(Date) as MonthName,

     Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter,

     QuarterName(Date) as QuarterName,

     Year(Date) as Year,

     WeekYear(Date) as WeekYear;  

LOAD Date(MinDate+IterNo()-1) as Date

While MinDate+IterNo()-1 <= MaxDate;

LOAD Min(Date) as MinDate,

     Max(Date) as MaxDate

Resident tabSourceOfDate;

[#4]

Thank you very much for your help!

I had actually already added a master calendar to the app. Using this I indeed get all values to show on the x-axis, including the ones on which there were no sales (which show up as 0).

However, this unfortunately only seems to work if I do not filter the data in any way. The moment I start filtering the data (for example, selecting one particular product, or one particular customer), all dates with zero sales disappear from the x-axis again, despite having the "display null values" and "show zero values" buttons checked. So while this solution is a step in the right direction, It's not yet entirely what I'm looking for.

The only solution that I have been able to come up with so far, is to add a 'dummy' measure that is always '0', and ignores any filter context. But this is not really ideal either...

[#5]

Can you explain how this solves the problem?

I asked a similar question (How to retain hierarchy view of null values?), but I think the answers to these questions are missing the point.

As soon as a filtering selection is made, all null values are removed from the x-axis. This is particularly undesirable with a date dimension.

[petter]

It is definitely something that is solvable - but it is very hard for me to imagine where the problem lies without having some kind of app that you can share... Are you able to share your app or a demo that illustrates your problem?

[petter]

It is a very important part of the solution. If you don't have values on your first dimension for every point that you possibly want to display there is no way of getting the the full x-axis. But it is not a full solution in itself. You have more than one way of solving the last part. You could either tweak some options or you could add values in your load script - either 0 values or some valid value copied from previous values or null values.

It is hard for me at least to explain "theoretically" so a simple test app with test data is the quickest way of demonstrating what to do...

[petter]

I have attached the demo app too:

[#9]

I think the crucial point here is the presence or absence of filtering.

I've tried to show this in the demo app I attached. The data structure is almost the same as yours, only I added a column "company" in the sales table. As you can see, when the data is unfiltered, all months are indeed displayed on the x-axis of the bar chart regardless of whether there were any sales (similar to your pictures). However, when you click the filter on the right to select only the data for company A or B, all months in which there were no sales disappear. This is what I would like to prevent.

[#10]

Hi Petter,

I've attached the sample from which the screen shots in my referenced post were taken.

Drill down to the day level in the bar chart, then press any segment in one of the donut charts. You'll see that only those days with values are shown in the bar chart.

(Ignore the horizontal line, it was testing ideas to try and force days to appear)

Ron.