topic Re: Max Value based on set analysis in App Development

Max Value based on set analysis

jlampard40 — Sat, 16 Nov 2024 03:51:48 GMT

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

Re: Max Value based on set analysis

sunny_talwar — Wed, 11 Dec 2019 14:20:00 GMT

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

Re: Max Value based on set analysis

jlampard40 — Thu, 12 Dec 2019 09:34:48 GMT

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

Re: Max Value based on set analysis

sunny_talwar — Thu, 12 Dec 2019 11:45:05 GMT

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

Re: Max Value based on set analysis

jlampard40 — Thu, 12 Dec 2019 12:25:44 GMT

Thank you Sunny!

Re: Max Value based on set analysis

jlampard40 — Thu, 12 Dec 2019 13:58:12 GMT

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

Re: Max Value based on set analysis

sunny_talwar — Thu, 12 Dec 2019 15:06:55 GMT

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

Re: Max Value based on set analysis

jlampard40 — Thu, 12 Dec 2019 15:29:00 GMT

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

Re: Max Value based on set analysis

sunny_talwar — Thu, 12 Dec 2019 15:30:18 GMT

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)

Re: Max Value based on set analysis

jlampard40 — Fri, 13 Dec 2019 10:26:15 GMT

That works - thanks so much Sunny!! Kind regards