Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Greg,
Try to apply only this expression:
Sum({<[Deal Type]={'Add-On'},[DXC Solution]-={'NSSR'}>-<[DXC Solution]={"*"}>} [Amount])
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])