Max Value based on set analysis

[jlampard40]

Hi guys - I'm trying to work out how to show, on a text box, the field which relates to the MAX value within my data.

I currently have the following set analysis:

=Count({<CP_DateofReport={'04/12/2019'},CP_ReportName={'Req to Att'}>}CP_EventKey)

-

Count({<CP_DateofReport={'27/11/2019'},CP_ReportName={'Req to Att'}>}CP_EventKey)(CP_DateofReport is the date of each report which goes out weekly; CP_ReportName is the specific report I need to refer to; CP_EventKey is the unique ID of each patient and I use this to count).

The difference between the two counts for each report gives me a value - the highest of these values will be what I want to show as being the MAX - as an example, if my value was 7, I'd like the NAME of the exam (CP_ExamCombined) to show in the text box rather than the value itself...

Any ideas?

Kind regards

[sunny_talwar]

Try this

FirstSortedValue(CP_ExamCombined, -Aggr(RangeSum(Count({<CP_DateofReport = {'04/12/2019'}, CP_ReportName = {'Req to Att'}>}CP_EventKey), -Count({<CP_DateofReport = {'27/11/2019'}, CP_ReportName = {'Req to Att'}>} CP_EventKey), CP_ExamCombined))

[jlampard40]

Hi Sunny - this is resulting in an error in the text box:

'Error:  Error in expression:  Nested aggregation not allowed'

Any ideas?

Rgds

=FirstSortedValue([CP_ExamCombined], -Aggr(RangeSum(Count({<[CP_DateofReport] = {'04/12/2019'},

[CP_ReportName] = {'Req to Att'}>}[CP_EventKey])), -Count({<[CP_DateofReport] = {'27/11/2019'},

[CP_ReportName] = {'Req to Att'}>} [CP_EventKey]), [CP_ExamCombined]))

[sunny_talwar · Accepted solution]

Try this (one of the parenthesis were misplaced)

=FirstSortedValue([CP_ExamCombined], -Aggr(RangeSum(Count({<[CP_DateofReport] = {'04/12/2019'}, [CP_ReportName] = {'Req to Att'}>}[CP_EventKey]), -Count({<[CP_DateofReport] = {'27/11/2019'}, [CP_ReportName] = {'Req to Att'}>} [CP_EventKey])), [CP_ExamCombined]))

[jlampard40]

Thank you Sunny!  

[jlampard40]

Hi Sunny - if I want to add the NEXT value down the list, is there any way I can do this?  So, using your great set analysis, I am pulling back the examcombined name of 'MPELV' which is indeed correct!  The next largest exam is also potentially causing us problems coming up, so I'd like to pull that one too into a text box.  Any ideas?  Many thanks as always.  Kind regards

[sunny_talwar]

May be this (Note the 2 at the end to pull the value associated with the 2nd largest number)

=FirstSortedValue([CP_ExamCombined], -Aggr(RangeSum(Count({<[CP_DateofReport] = {'04/12/2019'}, [CP_ReportName] = {'Req to Att'}>}[CP_EventKey]), -Count({<[CP_DateofReport] = {'27/11/2019'}, [CP_ReportName] = {'Req to Att'}>} [CP_EventKey])), [CP_ExamCombined]), 2)

[jlampard40]

Hi Sunny - that's pulling back a '-' in my text box, so not sure it's recognising it.  Any ideas?

[sunny_talwar · Accepted solution]

May be try adding a DISTINCT

=FirstSortedValue(DISTINCT [CP_ExamCombined], -Aggr(RangeSum(Count({<[CP_DateofReport] = {'04/12/2019'}, [CP_ReportName] = {'Req to Att'}>}[CP_EventKey]), -Count({<[CP_DateofReport] = {'27/11/2019'}, [CP_ReportName] = {'Req to Att'}>} [CP_EventKey])), [CP_ExamCombined]), 2)

[jlampard40]

That works - thanks so much Sunny!!  Kind regards