topic Re: Show zero values in bar chart in App Development

Show zero values in bar chart

danmcm — Mon, 20 Dec 2021 21:38:40 GMT

Hi all, I have the chart shown in the attachment. It is a count of all people in a population with dimensions as Age Group and Gender and a width measure written as an expression based on a count of people by gender. The sheet the chart is on allows for picking health conditions (say, Pregnancy or Depression or Cancer). Some of those conditions have no people in the age range so the chart smushes down and rather than 5 bars with zero values shows 3 or 4 bars. I would like to always show 5 bars and if no one is in the age range then the bar is zero length. I can't seem to find out how to do this. Note: this is Qlik Sense Enterprise and the age range is a column in our "person table". So in the table is, literally, 0-18 or 19-34 or 35-49 (etc.) depending on the person's age.

The current definition of the width of the bars is:

if (person_sex = 'M', count(distinct {<sex = {'M'}>}id)*-1, count(distinct{<sex={'F'}>}id))

I have tried this:

if (person_sex = 'M', count(distinct {<sex = {'M'}>}id)*-1 + SUM({1}0), count(distinct{<sex={'F'}>}id + SUM({1}0))

And this (formatted for my own readability):

if (person_sex = 'M',

if(count(distinct {<sex = {'M'}>}id) > 0,

count(distinct {<sex = {'M'}>}id)*-1,

0),

if(count(distinct {<sex = {'F'}>}id) > 0,

count(distinct {<sex = {'F'}>}id),

0))

I also tried substituting the zeroes for ones as the default just to try to see some kind of bar.

Any ideas or help is appreciated!

I read through this solution (and many others) but it didn't help me.

-dan

Re: Show zero values in bar chart

danmcm — Wed, 05 Feb 2020 16:45:42 GMT

Ok well....I have the solution! New expression:

if(person_sex = 'M',

count(distinct {<sex = {'M'}>}id) + sum({1}1)*0,

count(distinct {<sex = {'F'}>}id) * -1 + sum({1}1)*0)

and then under Add-ons -> Data handling I checked "Include zero values".

Whew!!

Re: Show zero values in bar chart

Kashyap_R — Thu, 06 Feb 2020 04:28:23 GMT

It is not working for me, If you have a sample QVF that is working can u attach the file

thanks in advance

Re: Show zero values in bar chart

danmcm — Mon, 10 Feb 2020 17:49:17 GMT

Sorry, I can't send my QVF file, it's internal/proprietary/whatever. Maybe I can help with your code (not your QVF)? I'm pretty new to this, but I'm catching on slowly.

Re: Show zero values in bar chart

torraroger — Fri, 14 Aug 2020 08:32:18 GMT

Hello,

I applied this to my QSense. (+ sum({1}1)*0) as following:

if((Count(Workbook)/Count(DISTINCT(User))+ sum({1}1)*0) > 0, Count(Workbook)/Count(DISTINCT(User)), 0)

This expression shows me the average visits on Workbooks, and If there's no visits, it shows 0. However, the chart is not affected by filters. I can't filter by month, or by other measures.

Thanks

Re: Show zero values in bar chart

ioannaiogr — Fri, 10 Sep 2021 11:07:25 GMT

This is true, by this method, it is not affected by any other filter selection. Do you know any solution to this? @danmcm Thanks in advance 🙂

Re: Show zero values in bar chart

danmcm — Tue, 19 Oct 2021 14:24:48 GMT

Sorry, I no longer have a license for Qlik, so I cannot test that code. But my solution did work and filtered as expected.

-dan