topic Re: Showing months in pivot and/or charts where data is null in App Development

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

leenlart — Thu, 10 Oct 2024 15:29:53 GMT

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!

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

sbaro_bd — Fri, 11 Oct 2024 09:59:54 GMT

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.

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

leenlart — Fri, 11 Oct 2024 12:38:27 GMT

Hello @sbaro_bd,

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

Any other ideas ? 🙂

Regards

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

Shouq — Fri, 11 Oct 2024 12:43:37 GMT

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)

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

leenlart — Fri, 11 Oct 2024 13:26:27 GMT

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!