SUM with Set Expression

[Anonymous]

In a chart expression, I want to sum a field, but only if the field is greater than 10.  In the record set I'm working with, there are four values - one is less than 10, and the rest are greater than 10.

The following expression definition results in a sum of 0 (which is not correct):

Sum({$< [PAWorkRVU]= {'>=10.0'}>} [PAWorkRVU])

The following expression definition results in a sum of all rows regardless of value (which is not correct).

Sum({$< [PAWorkRVU]= {'<=10.0'}>} [PAWorkRVU])

What am I doing wrong?

[sunny_talwar]

Can you share a list box object snapshot for the field PAWorkRVU?

[Anonymous]

Well, all of the values in the list are less than 10. It seems that my requirements may not have been sufficiently defined.

The chart in question is a pivot table that pivots on a date field, so lets assume that the values need to be summed for a current date before being compared to the control value (10). How would I do that?

[sunny_talwar]

May be try this:

If(Sum(PAWorkRVU) >= 10, Sum(PAWorkRVU))

[Anonymous]

That returns all rows. There is one date that has less a value than 10, and therefore should not be included in the result.

[sunny_talwar]

Don't really know unless you can share a sample or may be an image?

[Anonymous]

It turns out that my requirements were WILDLY misstated.

I need to count the number of dates where the monthly sum of PAWorkRVU was > 10. Something like this:

SUM( IF( SUM([Provider Aggregate Work RVU]) >= 10, 1, 0) )

[krishnacbe]

Hi,

Try below expression,

Count({$< [PAWorkRVU]= {'>=10.0'}>} [Month])

or

Count({$< [PAWorkRVU]= {'>=10.0'}>} distinct [Month])

[sunny_talwar · Accepted solution]

May be this:

Sum(Aggr(If(Sum(PAWorkRVU) > 10, 1, 0), Month))

[Anonymous]

That gives me 0, which is not correct. It has to be the sum of PAWorkRVU for a given date. I'm having trouble using functions in the set analysis expression. I think I need something like this (the following expression is invalid, but I think this is the way it needs to evaluate):

COUNT({$< SUM([PAWorkRVU])= {'>=10'} >} [CalendarDate])