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?

[Anonymous]

That was it.

[sunny_talwar]

If the above worked, you can use set analysis also

Count(DISTINCT {<Month = {"=Sum(PAWorkRVU) > 10"}>} Month)

[xyz1]

,

[Anonymous]

Well, I think it still needs tweaking. This is what I have:

Count(DISTINCT {<CalendarDate = {"=Sum([PAWorkRVU]) > 10"}>} CalendarDate)

When I view an unfiltered list, the value is incorrect. When I select just one row in the table, the value is correct. The data looks like this:

PROVIDER     CalendarDate     PAWorkRVU
Person1           01/01/2017         6.1
Person1          01/01/2017        10.5
Person1          02/01/2017        6.1
Person1          02/01/2017        10.5
Person1          03/01/2017        6.1
Person1          03/01/2017        10.5
Person1          04/01/2017        6.1  <<----- Less than 10 for 04/01/2017
Person2          01/01/2017        6.1
Person2          01/01/2017        10.5
Person2          02/01/2017        6.1
Person2          02/01/2017        10.5
Person2          03/01/2017        6.1
Person2          03/01/2017        10.5
Person2          03/01/2017        6.1
Person2          03/01/2017        10.5

The table I'm working with is supposed to show the following:

PROVIDER     PAWorkRVU     QualifyingMonths
Person1          85.9                   3 <<--- only three months meet the criteria
Person2          66.4                   4 <<--- four months meet the criteria

In this table PAWorkRVU shows the sum of all rows for the given PROVIDER, and the QualifyingMonths is the number of months where the total PAWorkRVU for a given month exceeds the value of 10.

When no providers are selected, Person1 shows 4 months that meet the criteria (which is obviously wrong). However, when I specifically select Person1 in the table, the value correctly shows 3.

[sunny_talwar]

I would then use the Aggr() function....

Sum(Aggr(If(Sum(PAWorkRVU) > 10, 1, 0), CalendarDate, PROVIDER))

or if you can create a new field in the script which combines CalendarDate and Provider like this

LOAD CalendarDate,

          PROVIDER,

         AutoNumber(CalendarDate&PROVIDER) as NewField,

          ....

FROM....

then you can use set analysis like this

Count(DISTINCT {<NewField = {"=Sum([PAWorkRVU]) > 10"}>} CalendarDate)

[Anonymous]

That appears to have done the trick. Many thanks.