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]

I had to add a "dummy" company that has no sales for all the months and no name. Adding a set expression which always includes/adds this dummy company will force Qlik Sense to always show all 12 months unless you de-select "Show Zero Values" in Add-Ons / Data handling for the chart.

[#3]

This works!

There's just one more thing though... the orders in my real database stretch a period of about five years and show the sales date as well as the month. With this solution, if I ever want the x-axis to be the 'sales date' rather than aggregating by month, I would have to add almost 2000 dummy entries to the table (i.e. one for each day). Is there a more effecient way of solving the problem than this?

[Anonymous]

Hi Stefan,

This is extremely late but one of my coworkers found a workaround to this exact problem:

In the set analysis of your measure (Sales) write:

if(Sum({1} [2nd bar graph dimension]) > 0, Sum([Sales]),0)

Now, when you make selections, all dates should remain displayed even if 0 products/ customers made an order during that month.

Hope this helps.

[petter]

About time to mark this question as answered now then?

[#6]

I know this thread is pretty old, but if you do something along the lines of sum(aggr(<expression>,[Year-Month])) it should work as well.

[egoss_cynosure_]

Nice and simple and works great!

[k_west]

I was able to implement the drill down work around:

Sum(ORDERS_RCVD)+0*sum({1}ORDERS_RCVD)

to get the zero's to show up where orders were not received.  The problem I am not having is I want to take the average over the entire time frame TO INCLUDE all the zeros.  Currently the average (once I drill down) is only taking the average of months with orders. 

Any thoughts?