Show zero values in bar chart

[danmcm]

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

[danmcm · Accepted solution]

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!!

[Kashyap_R]

Hi

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

thanks in advance

[danmcm]

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.

[torraroger]

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

[ioannaiogr]

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 🙂

[danmcm]

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