Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
rtr13
Contributor III
Contributor III

Input dash with text when variable is null

Hi Everyone, 

I'm currently working on a dashboard where I want to input a dash ( - ) when a value is null. Usually this is a default for Qlikview, but within my expression I combine the value with a label as pictured below. 

toggle3.PNG

The end goal is to have ' - days' instead of just days appear. I also want to avoid placing a zero in front of the days when a value is null because it can be misleading to end users because zero days is different than the null value. 

Currently, I am using the following expression to calculate the numbers within the pivot chart. There is a toggle for the pivot chart to show different values, hence the combined if statement. 

=if(ShowID_US1='d',Num(Sum({<Year={'Previous Year'}>}TotalDays)/Sum({<Year={'Previous Year'}>}TotalUD),'##.0') & ' days',
if(ShowID_US1='h',Num(Sum({<Year={'Previous Year'}>}TotalDays)/Sum({<Year={'Previous Year'}>}TotalUD),'#,##0') & ' hours'))

I appreciate any help! Thank you!

Labels (2)
1 Solution

Accepted Solutions
StarinieriG
Partner - Specialist
Partner - Specialist

Hi,

maybe you could try something like this

 

=if(ShowID_US1='d',
If(Len(Num(Sum({<Year={'Previous Year'}>}TotalDays)/Sum({<Year={'Previous Year'}>}TotalUD),'##.0'))=0,'-',
Num(Sum({<Year={'Previous Year'}>}TotalDays)/Sum({<Year={'Previous Year'}>}TotalUD),'##.0')) & ' days',
if(ShowID_US1='h',
If(Len(Num(Sum({<Year={'Previous Year'}>}TotalDays)/Sum({<Year={'Previous Year'}>}TotalUD),'#,##0'))=0,'-',
Num(Sum({<Year={'Previous Year'}>}TotalDays)/Sum({<Year={'Previous Year'}>}TotalUD),'#,##0')) & ' hours'))

View solution in original post

3 Replies
nisha_rai
Creator II
Creator II

Hi,

Check mark the Suppress zero values i presentation tab. May it helps

rtr13
Contributor III
Contributor III
Author

Although the value is null, I don't want to suppress the value, instead I want to make it a dash. The problem comes in with combining the numeric and string characters within the expression that I am unsure how to deal with.

StarinieriG
Partner - Specialist
Partner - Specialist

Hi,

maybe you could try something like this

 

=if(ShowID_US1='d',
If(Len(Num(Sum({<Year={'Previous Year'}>}TotalDays)/Sum({<Year={'Previous Year'}>}TotalUD),'##.0'))=0,'-',
Num(Sum({<Year={'Previous Year'}>}TotalDays)/Sum({<Year={'Previous Year'}>}TotalUD),'##.0')) & ' days',
if(ShowID_US1='h',
If(Len(Num(Sum({<Year={'Previous Year'}>}TotalDays)/Sum({<Year={'Previous Year'}>}TotalUD),'#,##0'))=0,'-',
Num(Sum({<Year={'Previous Year'}>}TotalDays)/Sum({<Year={'Previous Year'}>}TotalUD),'#,##0')) & ' hours'))