Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
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?