topic Re: Help with expression in QlikView

Help with expression

Wed, 05 Sep 2012 17:36:01 GMT

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)

Re: Help with expression

whiteline — Wed, 05 Sep 2012 18:14:07 GMT

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

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

Re: Help with expression

Jason_Michaelides — Wed, 05 Sep 2012 18:18:18 GMT

You could try:

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

Hope this helps.

Jason

Re: Help with expression

whiteline — Wed, 05 Sep 2012 18:22:38 GMT

It seems you have incorrect set analysis syntax.

Re: Help with expression

Jason_Michaelides — Wed, 05 Sep 2012 21:49:19 GMT

Cheers - a few too many {}...

Re: Help with expression

Wed, 05 Sep 2012 21:50:16 GMT

Thanks everyone!

Re: Help with expression

Wed, 05 Sep 2012 22:43:53 GMT

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)

Re: Help with expression

jayaramp — Thu, 06 Sep 2012 03:14:43 GMT

Hi Rebeccad,

Take off double quotes around 2011, it should work.

Hope that works.

Thanks,

Jay

Re: Help with expression

whiteline — Thu, 06 Sep 2012 05:34:19 GMT

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)

Re: Help with expression

Jason_Michaelides — Thu, 06 Sep 2012 06:21:32 GMT

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)

Re: Help with expression

whiteline — Thu, 06 Sep 2012 08:54:49 GMT

In this case I think you will need the brackets:

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