Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Force showing all values on x-axis

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!

16 Replies
petter
Partner - Champion III
Partner - Champion III

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.

2015-07-08 x-axis all values #3.PNG

2015-07-08 x-axis all values #4.PNG

Not applicable
Author

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
Not applicable
Author

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
Partner - Champion III
Partner - Champion III

About time to mark this question as answered now then?

Not applicable
Author

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_
Contributor II
Contributor II

Nice and simple and works great!

k_west
Contributor
Contributor

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?