Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help with expression

Hi All,

I have an expression that adds Commissions when all three fees are greater than zero. Can somebody help me adjust it so that it adds Commissions when any of the three fees are greater than zero? Thanks!!

Sum({<FeeOne = {">0"},FeeTwo = {">0"},FeeThree = {">0"}>} Commissions)

1 Solution

Accepted Solutions
whiteline
Master II
Master II

You can add sets (look at help, set analysis):

=Sum({<FeeOne = {">0"}>+<FeeTwo = {">0"}>+<FeeThree = {">0"}>} Commissions)

View solution in original post

10 Replies
whiteline
Master II
Master II

You can add sets (look at help, set analysis):

=Sum({<FeeOne = {">0"}>+<FeeTwo = {">0"}>+<FeeThree = {">0"}>} Commissions)

Jason_Michaelides
Luminary Alumni
Luminary Alumni

You could try:

Sum({<FeeOne = {">0"}>}+{<FeeTwo = {">0"}>}+{<FeeThree = {">0"}>} Commissions)

Hope this helps.

Jason

whiteline
Master II
Master II

It seems you have incorrect set analysis syntax.

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Cheers - a few too many {}...

Not applicable
Author

Thanks everyone!

Not applicable
Author

One more question... I now need to make it for Year=2011. Is this possible? I tried this but it didn't work:

=Sum({<FeeOne = {">0"}>+<FeeTwo = {">0"}>+<FeeThree = {">0"},Year={"2011"}>} Commissions)

jayaramp
Contributor II
Contributor II

Hi Rebeccad,

Take off double quotes around 2011, it should work.

Hope that works.

Thanks,

Jay

whiteline
Master II
Master II

Read the help about set analysis to better understad what you are doing.

{<> + <> + <>}

Between each of <> is a modfied current set of data. There are three different sets combined together using union opperation. So if you want only 2011 year you should modify each of them:

=Sum({<FeeOne = {">0"},Year={"2011"}>+<FeeTwo = {">0"},Year={"2011"}>+<FeeThree = {">0"},Year={"2011"}>} Commissions)

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Or I think you can use the * to get just the intersecting records. I'm not sure if you'd have to put the added bit after each set - try it out:

=Sum({<FeeOne = {">0"}>+<FeeTwo = {">0"}>+<FeeThree = {">0"}>*<Year={"2011"}>} Commissions)