Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
danmcm
Partner - Contributor II
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
danmcm
Partner - Contributor II
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!!

View solution in original post

6 Replies
danmcm
Partner - Contributor II
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!!

kashyap311996101
Partner - Specialist
Partner - Specialist

Hi

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

thanks in advance

Thanks and Regards
Kashyap.R
danmcm
Partner - Contributor II
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.

torraroger
Contributor III
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

ioannaiogr
Creator
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 🙂

danmcm
Partner - Contributor II
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