Help with expression

[#1]

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)

[whiteline · Accepted solution]

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

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

[Jason_Michaelides]

You could try:

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

Hope this helps.

Jason

[whiteline]

It seems you have incorrect set analysis syntax.

[Jason_Michaelides]

Cheers - a few too many {}...

[#6]

Thanks everyone!

[#7]

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]

Hi Rebeccad,

Take off double quotes around 2011, it should work.

Hope that works.

Thanks,

Jay

[whiteline]

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]

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)