Sum when multiple fields are null

[jgreen95]

Struggling to find a solution for this:

I want to Sum -  ORIGINAL_CHARGE

when both the below fields are null

AUTHORISED_DATE

CANCELLATION_DATE

is this possible? i can only find solutions for when 1 field is null not multiple.

[Kushal_Chawda · Accepted solution]

try this below expression

Sum({<AUTHORISED_DATE={"=len(trim(AUTHORISED_DATE))"},CANCELLATION_DATE={"=len(trim(CANCELLATION_DATE))"}>}ORIGINAL_CHARGE)

or better to create Flag in script and refer that in expression

LOAD *,l

           if( len(trim(AUTHORISED_DATE)) =0 and len(trim(CANCELLATION_DATE))=0,1,0) as Null_Flag

FROM QVD

then use below expression

Sum({<Null_Flag={1}>}ORIGINAL_CHARGE)

[jgreen95]

when i use:

Sum({<F1={"=len(trim(AUTHORISED_DATE))"},F2={"=len(trim(CANCELLATION_DATE))"}>}ORIGINAL_CHARGE)

it is summing all the rows in the table, not just where those 2 fields are null

[Kushal_Chawda]

See I have updated my reply in previous post

[jgreen95]

it is now counting but not counting the correct amount, i have used the script in the load editor and that has worked.