Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jgreen95
Contributor III
Contributor III

Sum when multiple fields are null

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.

1 Solution

Accepted Solutions
Kushal_Chawda

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)

View solution in original post

4 Replies
Kushal_Chawda

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)

View solution in original post

jgreen95
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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