topic Re: Showing null value in bar chart (do not suppressing empty rows) in App Development

Showing null value in bar chart (do not suppressing empty rows)

Newbie777 — Sun, 26 Feb 2023 07:45:28 GMT

Hello,

I'd like to show all the month-year bar chart even when they have null (zero) values to be able to see the costs incurred by year month even when a particular year-month has zero value. It is important to show the information without skipping for zero value year month. However, the default Qlik Sense visualization suppresses zero (null) bars. I tried to search in the community, but I couldn't solve this issue. I'd greatly appreciate it if you could help me with this.

Thank you for your help.

Re: Showing null value in bar chart (do not suppressing empty rows)

BrunPierre — Sun, 26 Feb 2023 08:35:33 GMT

Hi @Newbie777

In QS, you can do this by either unchecking 'include zero values' under the dimension or under Add-Ons-> Data handling.

Re: Showing null value in bar chart (do not suppressing empty rows)

Newbie777 — Sun, 26 Feb 2023 09:07:07 GMT

Thanks, but I already did exactly that and the bar chart is still showing suppressing zero value bars. I am not sure why. When I initially created the bar chart, it was showing empty month as well and it is more meaningful to see in that way but it disappeared and got suppressed without me doing anything and after that, I can't get back to the previous way of presentation.

Re: Showing null value in bar chart (do not suppressing empty rows)

BrunPierre — Sun, 26 Feb 2023 10:51:49 GMT

Try adding "+Sum({1} 0)" to the expression, month year without amount should display 0

Re: Showing null value in bar chart (do not suppressing empty rows)

Newbie777 — Sun, 26 Feb 2023 11:20:55 GMT

Thanks FrunPierre,

I've added "+Sum({1} 0)" to the measure expression, but it is still not working. I tried both ticking and unticking in the Year Month dimension and Add-on, but it is not showing January 2023 value.

You've mentioned I am supposed to untick the 'include zero values', but it didn't work, so I tried the opposite too, but it didn't work either. I'd greatly appreciate it if you could shed light to this issue.

Re: Showing null value in bar chart (do not suppressing empty rows)

barnabyd — Sun, 26 Feb 2023 21:02:41 GMT

G'day @Newbie777,

You need to create a Master Calendar table to fix this. Qlik is only displaying columns in your chart if there is at least one row of data in the table. If you create a linked table that contains every possible date in the range of interest, and use the date from this table as your horizontal axis, then your chart will show a bar for every date.

There are many tutorials on the forum or YouTube - here is one the first one I just looked up.

I hope this helps.

Cheers, Barnaby

Re: Showing null value in bar chart (do not suppressing empty rows)

Newbie777 — Mon, 27 Feb 2023 01:45:35 GMT

Hi Barnaby,

Thanks for responding to my question. I have in fact been using the calendar table imported from Excel with all the dates in a year with Year Month field, too, so I am not sure why this is not working. Strange thing is that when I initially created the visualization, it was showing the bar (space with no value) for January 2023, and from that, I was able to gain visualization where costs were missing, and it was useful to see the information in that way, but when I opened the app again, the blank value Year Month got suppressed, disguising the information. If you are not paying careful attention, you will not see that the costs are missing in some months because there's no blank space anymore. I tried everything which I could find from doing quick search on the internet. I'd greatly appreciate it if anyone could shed light as to why it was showing zero value bars to begin with and then suddenly start suppressing without me doing anything in particular to change the setting.

Thank you & best regards,

Re: Showing null value in bar chart (do not suppressing empty rows)

BrunPierre — Mon, 27 Feb 2023 07:08:58 GMT

If you don't mind, could you share some sample data and your chart expression?

Re: Showing null value in bar chart (do not suppressing empty rows)

Newbie777 — Mon, 27 Feb 2023 11:22:04 GMT

Thanks BrunPierre, but subsequently my company's Qlik Sense Helpdesk people informed me that it was due to the following problem, and after I fixed that and ticked both include zero tickbox in Data section and Add-ons section, the problem was solved!