Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

greg_zajic
New Contributor

Getting Null Values to be included IN the expression

Null values are causing me problems in expressions.

    Totals

    $10 NSSR

    $85 Various categories

    $5 Null (blank values)

    $100 Total

Sum({<[Deal Type]={'Add-On'}>} [Amount])

Value = $100 (as expected)

Formula I initially used:

Sum({<[Deal Type]={'Add-On'},[DXC Solution]=-{'NSSR'}>} [Amount])

Value = $85 which is Total for Add-On Less the NSSR, but the Null/blank values are also excluded.

Any Idea how to update the formula above so that the Null values ARE included

Below is a temporary workaround but there has to be a better way and this will not work in certain more complicated situations

Sum({<[Deal Type]={'Add-On'}>} [Amount]) - Sum({<[Deal Type]={'Add-On'},[DXC Solution]={'NSSR'}>} [Amount])

Any assistance is appreciated.

Greg

2 Replies
thiago_justen
Valued Contributor III

Re: Getting Null Values to be included IN the expression

Greg,

Try to apply only this expression:

Sum({<[Deal Type]={'Add-On'},[DXC Solution]-={'NSSR'}>-<[DXC Solution]={"*"}>} [Amount])

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago

Re: Getting Null Values to be included IN the expression

One option is to create a flag in the script

LOAD [DXC Solution],

    If(Len(Trim([DXC Solution])) > 0, 1, 0) as NullDXCSolutionFlag

and then this

Sum({<[Deal Type]={'Add-On'}, [DXC Solution] = -{'NSSR'}>+<[Deal Type]={'Add-On'}, NullDXCSolutionFlag = {1}>} [Amount])

Community Browser