Show 0 if values do not exist (example 0 sales on a specific date)

[Bellamy]

Hello everyone, I am struggling how figure it out how to show in a chart, or table, a "0" value for all the missing sales.

I have a big order table, as a maximum detail I have the product ID, product category, then customers, area  and other dimensions.

Since I need to perform a 12 months rolling on a table and also a chart, I need that the sales from each product code (or from product category, or customers, and so on) is considered even when in a specific date there were no sales at all.
Example, if there is sales in 01/01/2021 , 05/03/2021 for a specific product, customer and so on, I would like the all calcs take in account that in 02/01/2022 until 04/03/2022 the sales were 0.
This because when I perform a 12 months rolling (for example) it's not correct it skip all the months with no data, but it should consider the months with no data as 0.

How can I perform this? I also tried to create a master calendar to link it to my order table (order date), but it's not working. It's enough to select any dimension, for example a product, or a customer, that I only can see the few points in which there is a value.

Searching for this issue I see topics raised even more than 10 years ago, is it possible that Qliksense did not developed something that help on this?

Thanks in advance

[rubenmarin]

Hi, you will need a relation between the fields you need in the table, to show a row with Date Z, product Y and customer X this relation should exists in your data model.

Take a look at the "Generating all combinations of several fields" example of this pdf to get the idea: https://community.qlik.com/cyjdu72974/attachments/cyjdu72974/qlikview-documents/8117/1/Generating%20...

Also, be sure to have checked the option to show zero values in the add-ons area of the properties panel.

[Bellamy]

Hi Rubenmarin, thank you for the answer.. I am going to read the pdf you linked.

So as far as I understood, there is not such a "automatic" way Qliksense developed this feature, but it has to be created each time.

At the moment I am going for a "quick" solution that consist in Load distinct all the customers joining distinct all the date and joining distinct all the products.
Then, in the "fact" table I've created an ID in order to move the real fact to the new huge table with all the combinations. The null ones become a 0 and this should works.

Obviously this method is not good for performance if the dimensions (customer,product,date) are many and I have to sacrifice the time-period using a Year-Month (thinking to do such table for year-month-day is crazy since it will generate billions of row more).

I really hope that someday Qliksense could develop a way that do not force developers to create a "fake" set of data and fullfill it with "0" . 

[rubenmarin]

Hi, note that this is to have data in all dates for all products and customers and maybe this is not needed for the analysis. Maybe the as-of calendar may also help: https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130