Difficulty with AGGR()

[proctors]

Hi.

I have an expression that only works when I select an individual person, but when I take the filter off and show all of the individuals, the column goes to null.

Expression is

sum([RVU WORK])/((vThresholdGoal/12)*vToDateMonths)

Dimensions are

Department, Billing Provider, Service Provider, and Place of Service.

I'm guessing I need aggr(), but I'm not sure how to get it to work. Please see attachment, last column of the chart. Data scrambled, SA should be off.

[vishsaggi]

what value does your vFTE_Est should have? I can see nothing in this variable?

[proctors]

My apologies, I stripped out a piece where the user could input an estimated number from 0.1 to 1.0. You can change it if that helps solve the problem of the last column. That variable that you're looking at works fine. If you select only one provider. See this updated QVW with more data. Make the following selections and you will see the data for one provider.

[sunny_talwar]

Would you be able to let us know what the expected output needs to look like for the sample provided?

Also, I noticed that some of the columns have no data at all... for example YTD Avg is all null and so is Threshold

[proctors]

Have you tried the second file?

[sunny_talwar]

So, I entered 0.1 for vFTE_Est... but based on the selection I see this

Are you seeing something different? If you are not... what do you expect to see here?

[proctors]

Please change to FY 18. Sorry it's not so easy, there are piece of data that are not available for 2019.

[sunny_talwar · Accepted solution]

Try now

[proctors]

Works. What did you change?

[sunny_talwar]

I changed the variable definition for vThresholdGoal

If(vFTE_Est = null() OR vFTE_Est < 0, [YTD Avg], vFTE_Est)*if(EST_Threshold <> Null() OR EST_Threshold < 0, EST_Threshold, Sum(TOTAL <[Cost Center], [Billing Provider]>Threshold))

and changed the expression to this

=Sum([RVU WORK])/(($(vThresholdGoal)/12)*vToDateMonths)