topic Input dash with text when variable is null in QlikView

Input dash with text when variable is null

rtr13 — Sat, 16 Nov 2024 01:11:03 GMT

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!

Re: Input dash with text when variable is null

nisha_rai — Thu, 27 Feb 2020 11:02:20 GMT

Hi,

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

Re: Input dash with text when variable is null

rtr13 — Thu, 27 Feb 2020 13:11:41 GMT

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.

Re: Input dash with text when variable is null

StarinieriG — Thu, 27 Feb 2020 13:19:13 GMT

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