# App Development

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for
Did you mean:
Partner - Contributor II

## Show zero values in bar chart

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

1 Solution

Accepted Solutions
Partner - Contributor II
Author

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

6 Replies
Partner - Contributor II
Author

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

Partner - Specialist

Hi

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

Thanks and Regards
Kashyap.R
Partner - Contributor II
Author

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.

Contributor III

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

Creator

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 🙂

Partner - Contributor II
Author

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

Tags
Community Browser