topic Re: How to get a conditional sum()? in App Development

How to get a conditional sum()?

Sun, 27 Mar 2016 00:49:47 GMT

I have a table that looks like:

Category	MyFlag	Amt
A	y	20.00
A	y	25.60
B	n	30.32
C	n	70.00
C	y	9.10

I want a measure that computes the sum of the selected records where MyFlag='y'. E.g. if all five are selected the measure is 20+25.6+9.10. The expression I am using is Sum({$<MyFlag='y'>}Amt), but it always shows 0 (zero). Any ideas for what I'm doing wrong? The expression editor doesn't show any syntax errors.

Thanks

Re: How to get a conditional sum()?

oknotsen — Sun, 27 Mar 2016 08:28:37 GMT

The correct Set Analysis statement should be this:

sum({$ < MyFlag = {'y'} > } Amt)

Notice the missing curly brackets in your statement .

Re: How to get a conditional sum()?

Chanty4u — Sun, 27 Mar 2016 08:31:16 GMT

try this

=Sum( {<MyFlag ={'y'} >} Amt)

Re: How to get a conditional sum()?

sunny_talwar — Sun, 27 Mar 2016 22:03:55 GMT

You mentioned about selection, if the sum needs to change based on selection, then I would try this:

=Sum({<Flag *= {'y'}>}Amt)

Re: How to get a conditional sum()?

Mon, 28 Mar 2016 04:50:12 GMT

Thanks, but that didn't make any difference.

Re: How to get a conditional sum()?

Mon, 28 Mar 2016 16:15:46 GMT

I had actually tried that earlier and it didn't work. When I tried it again didn't work initially. However, when I X'ed out my existing selections, then it worked.

Re: How to get a conditional sum()?

sunny_talwar — Mon, 28 Mar 2016 16:25:48 GMT

Well this expression will give you 0 if you select Flag = n, whereas the one provided by others will continue to show you y rows even if you select Flag = n

Not sure if you want this or not

Re: How to get a conditional sum()?

Mon, 28 Mar 2016 16:30:32 GMT

Good to know. I'm sure it will come in handy. Thanks.

Re: How to get a conditional sum()?

sunny_talwar — Mon, 28 Mar 2016 16:39:20 GMT