Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
You can add sets (look at help, set analysis):
=Sum({<FeeOne = {">0"}>+<FeeTwo = {">0"}>+<FeeThree = {">0"}>} Commissions)
You can add sets (look at help, set analysis):
=Sum({<FeeOne = {">0"}>+<FeeTwo = {">0"}>+<FeeThree = {">0"}>} Commissions)
You could try:
Sum({<FeeOne = {">0"}>}+{<FeeTwo = {">0"}>}+{<FeeThree = {">0"}>} Commissions)
Hope this helps.
Jason
It seems you have incorrect set analysis syntax.
Cheers - a few too many {}...
Thanks everyone!
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)
Hi Rebeccad,
Take off double quotes around 2011, it should work.
Hope that works.
Thanks,
Jay
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)
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)