Input dash with text when variable is null

[rtr13]

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. 

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!

[nisha_rai]

Hi,

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

[rtr13]

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 · Accepted solution]

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'))