Showing months in pivot and/or charts where data is null

[leenlart]

Hello, 

I am building a page to analyse credit notes.  I'm using Qlik Sense Enterprise May 2024.  

We'd like to show a line chart showing the total amount of credit notes by month, vs total amount last year of credit notes.  

We also have a data table with related data.  

The problem is that we'd like to see all the months show, even if there is no data.  

I've checked the show null values, and include 0 values options, but I've had to do a work around to see all the months.  For example : 

sum(credit_notes) ==> if(isnull(sum(credit_notes)),0,sum(credit_notes))

This works fine, until the user filters something like sales team, or product type, etc.  

I'm using a calendar table, so I know that all the months exist in my data.  

Why is this so laborious ?  Sometimes where there is no data is just as revealing as where there is.  Also as humans, we tend to need to see all months in a row to quickly make sense of what we're reading.  

Does anyone have a solution to this issue ?  

Thanks for your help!  

[sbaro_bd]

Hi @leenlart,

If you want to get this result, your calendar table has to be a "master calendar". See the solution on this post : 
https://community.qlik.com/t5/QlikView-App-Dev/How-to-Create-Master-Calendar/td-p/103109

Regards.

[leenlart]

Hello @sbaro_bd, 

Thanks for your reply, but I'm already using a master table.  

Any other ideas ?  🙂

Regards

[Shouq]

Hello,

Unfortunately a master calendar would work only without filters, once you apply filters, you'll lose the null months / years again. Instead, you can try adding this + sum({1} 0) to the end of your code. This will add a zero regardless of any filters. Let me know if it works 🙂

sum(credit_notes)+ sum({1} 0)

[leenlart · Accepted solution]

The problem with  sum({1} 0) is that it will then show all of the months, for all of the years of my master table that are loaded to the appli.  

The objects I'm trying to create will show the months for the civil year or the fiscal year (based on the user selection).  

I've tried adding this + sum({$} 0).  

It seems to work correctly in my application.  However it doesn't work in my test application.  

Thanks for your help!