topic Re: sum based on multiple null conditions in App Development

sum based on multiple null conditions

kaustubh — Mon, 04 Mar 2019 16:43:54 GMT

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.

Re: sum based on multiple null conditions

y_grynechko — Mon, 04 Mar 2019 22:17:22 GMT

Hey,

I am not sure what are you asking for.

For example if your table looks like this:

what do you want to accomplish?

Re: sum based on multiple null conditions

Zhandos_Shotan — Tue, 05 Mar 2019 04:39:51 GMT

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

Re: sum based on multiple null conditions

kaustubh — Fri, 08 Mar 2019 18:37:09 GMT

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.