replacing hypen with zero in table

[berryandcherry6]

Hi,

i have an expression where i am formatting column values in table. Now i need to replace hypen with 0, if value is null

Num(if(Metrics='Invitations',if(Sign($(=Count({$<batch={'23','34'}>}invitation_id))=-1,0,$(=Count({$<batch={'23','34'}>}invitation_id)),

if(Metrics='Delivered',Alt($=(Sum(delivered)),0),

if(Metrics='Completions Rate',Sum(completions)/

Sum(delivered),))), if(Metrics='Completions Rate','#,##0.0%', '#,##0'))

How could i do this?

[berryandcherry6]

HI,

It may be because i am using FirstSortedValue in expression, alt or isnull is not working.

I have attached sample app, Pleas look into it

Regards,

Supriya

[berryandcherry6]

Hi,

Thanks for reply

i used same in my code, it is not working i tried with both isnull or alt no result

I have attached sample please look into it.

Regards,

Supriya

[Karim_Khan]

Hi Supriya,

  PFB

=ALT(Num(if(BUSINESS_POLICY='Life Quote',Count(POLICY_NUM),

if(BUSINESS_POLICY='Life Policy',Sum(POLICY_NUM),

if(BUSINESS_POLICY='Health Policy',Sum(SR_NUM)/

Sum(POLICY_NUM),))), if(BUSINESS_POLICY='Annuity Contract','#,##0.0%', '#,##0')),'0')

Just change your Dimension names

Regards,

KK

[shraddha_g]

Use this in Load Script before loading the table

NullAsValue *;

SET NullValue = '0';

[berryandcherry6 · Accepted solution]

Hi guys,

first of all, i have to say sorry, because my expressions in query were not exactly same as i had posted and working expression.

i figured it out, why i was having this issue, the reason is

1.) i was using Dollar-sign Expansion with expression.

2.)If i need to use alt() or RangeMax() or sign() , i should not use Dollar-sign Expansion with expression, inside these(alt() or RangeMax() or sign()) functions.This may lead to undefined result, which was causing me to give hypen.

3.)If in single row, your expresion contains alt() or sign() function with dollar expansion, then all rows in that particular column can result as not valid, which will be shown in hypen symbol

My expression was

if(Sign($(=Count({$<batch={'23','34'}>}invitation_id))=-1,0,$(=Count({$<batch={'23','34'}>}invitation_id))

Alt($=(Sum(delivered)),0)

In my above expression i should remove ' $(= '  Dollar expansion symbol, then you get the result.

Thankyou all of you, to answering or giving hint for  my query.

[berryandcherry6]

HI Anand

I am using Qliksense, in chart properties, under Appearance i wont find anything you mentioned in your reply like to Populate Missing Cells. Is it possible in qliksense?

[its_anandrjs]

In Qliksense this type of data Handling is already maintain or you can also make conditions for this to handle as well as

Have a look on this section

Regards

Anand