Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kaustubh
Creator
Creator

sum based on multiple null conditions

Hello Everyone,

I have to perform a sum based on following conditions:

First result should come from first condition as:

(refcode -= NULL)

and add the above result to result from this second condition:

refcode = NULL AND (refnum_in1 -= NULL OR refnum_in2 -= NULL)

 

All the three are columns of same table.

Please help.

1 Solution

Accepted Solutions
Zhandos_Shotan
Partner - Creator II
Partner - Creator II

Hi!

According to example table provided by @y_grynechko  + Sales field as measure, try something like this:

Sum({<refcode= {"*"}  Sales)    //  refcode-= NULL 

+

// and add the above result to result from this second condition:

// refcode = NULL AND (refnum_in1 -= NULL OR refnum_in2 -= NULL)

Sum({<ID={"=Len(refcode)=0"}, refnum_in1={"*"}>} Sales) + Sum({<ID={"=Len(refcode)=0"}, refnum_in2={"*"}>} Sales)

 

Best Regards

View solution in original post

3 Replies
y_grynechko
Creator III
Creator III

Hey,

I am not sure what are you asking for. 

For example if your table looks like this:

Przechwytywanie.PNG

what do you want to accomplish?

 

Zhandos_Shotan
Partner - Creator II
Partner - Creator II

Hi!

According to example table provided by @y_grynechko  + Sales field as measure, try something like this:

Sum({<refcode= {"*"}  Sales)    //  refcode-= NULL 

+

// and add the above result to result from this second condition:

// refcode = NULL AND (refnum_in1 -= NULL OR refnum_in2 -= NULL)

Sum({<ID={"=Len(refcode)=0"}, refnum_in1={"*"}>} Sales) + Sum({<ID={"=Len(refcode)=0"}, refnum_in2={"*"}>} Sales)

 

Best Regards

kaustubh
Creator
Creator
Author

The code should first check the refcode column. If there are no nulls then those charges will be picked up to sum, then if it is null it will goto refnum_in1 and repeat same and add those charges to the cumulative sum of charges picked up by refcode. If both refcode and refnum_in1 are found to be null then refnum_in2 is checked and if value exists then its corresponding charge is picked up to add to the charges picked up by first two.

Note: The priority of code should flow from refcode to refnum_in2.