topic Re: Difficulty with AGGR() in QlikView

Difficulty with AGGR()

proctors — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Difficulty with AGGR()

vishsaggi — Wed, 01 Aug 2018 21:34:38 GMT

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

Re: Difficulty with AGGR()

proctors — Thu, 02 Aug 2018 13:04:00 GMT

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.

Re: Difficulty with AGGR()

sunny_talwar — Thu, 02 Aug 2018 13:15:48 GMT

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

Re: Difficulty with AGGR()

proctors — Thu, 02 Aug 2018 13:19:31 GMT

Have you tried the second file?

Re: Difficulty with AGGR()

sunny_talwar — Thu, 02 Aug 2018 13:31:26 GMT

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?

Re: Difficulty with AGGR()

proctors — Thu, 02 Aug 2018 13:34:04 GMT

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

Re: Difficulty with AGGR()

sunny_talwar — Thu, 02 Aug 2018 13:46:04 GMT

Try now

Re: Difficulty with AGGR()

proctors — Thu, 02 Aug 2018 14:33:17 GMT

Works. What did you change?

Re: Difficulty with AGGR()

sunny_talwar — Thu, 02 Aug 2018 14:49:13 GMT

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)

Re: Difficulty with AGGR()

proctors — Thu, 02 Aug 2018 15:03:04 GMT

Right now we have a Cycle group that includes cost center. Is there a way to make it dynamic such that the part that is underlined looks at the current cycle field? (e.g., Department, Cost Center, Program)

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))

Re: Difficulty with AGGR()

sunny_talwar — Thu, 02 Aug 2018 15:18:35 GMT

May be this

If(vFTE_Est = null() OR vFTE_Est < 0, [YTD Avg], vFTE_Est)*if(EST_Threshold <> Null() OR EST_Threshold < 0, EST_Threshold, Sum(TOTAL <$(='[' & GetCurrentField([Group Name]) & ']'), [Billing Provider]>Threshold))