topic Re: Aggregating {1} back To Selected Dimensions in App Development

Aggregating {1} back To Selected Dimensions

sgagliardi — Fri, 13 Aug 2021 19:38:13 GMT

Hello All,

I have what seems like a simple enough problem to solve, but I can't seem to crack it...

I am working on a dashboard where a user would establish an index selection of records through their selections, but be able to see rates, metrics, and measures against particular dimensions of the selected that factor in data and dimensions that may be excluded from the selections. I am trying to pull this all into a straight table object.

Example:

I want to see a list of attending physicians as my dimension in a table for whatever population of patient encounters that I have selected currently. As my measure, I would like to see a count of all encounters for the patients I have selected, but aggregated back to the original list of attending physicians I have in my first column. These encounter records may or may not have the same attending physicians, but have the same patient ID associated with them.

In column 1 I have the following expression in place to show only those attending physicians associated with my selections:

=aggr(if(count(distinct [Patient ID])>0,[Attending Physician Name]),[Attending Physician Name])

I have null() excluded here, so that when I add my count measure, a null() row will not present.

In column 2 I have the follow expression to return the count of all encounters for the patients captured by my selections:

=aggr(count({1}distinct [Patient Encounter]),[Patient ID])

I want to aggregate the full count back to the original physician rows associated with the patient id in my selection, but I'm finding that those encounters with physicians outside of my selection are being excluded from the measure because I have suppressed null() on this dimension, and that is how they would be categorized.

Re: Aggregating {1} back To Selected Dimensions

stevejoyce — Fri, 13 Aug 2021 20:19:01 GMT

I don't 100% follow. But if user is selecting [Attending Physician Name], you can have...

count({1 <[Attending Physician Name] = P([Attending Physician Name])> } [Patient Encounter])

to re-apply the [Attending Physician Name] filter.

Re: Aggregating {1} back To Selected Dimensions

sgagliardi — Fri, 13 Aug 2021 22:09:25 GMT

Thank you @stevejoyce for your reply.

I had experimented with p() in the set analysis as well, but I am getting the same result. It will only return a partial count in my table, unless null()s are turned on for that dimension, in which case all orphaned rows are summed under a null() row rather than be distributed back across the attending physician values of the selected patient id attending physician value.

To maybe clarify the problem further, let's say my data looks something like this:

[Patient ID],[Patient Encounter],[Attending Physician Name]

Bob, 27382, Dr. Fred

Sally, 27372, Dr. Fred

Dan, 37648, Dr. Rose

Bob, 64830, Dr. Albert

Bob, 53729, Dr. Rose

Sally, 37293, Dr. Fred

What I would like to have happen, is when I have made a selection on [Patient Encounter] '27382' to see in my table:

Dr. Fred | 3

I want for when I select the [Patient Encounter] '27382' associated with [Patient ID] 'Bob', for all encounters that Bob has (3 total) to tally beneath the attending physician of the patient encounter I actually have selected.

Re: Aggregating {1} back To Selected Dimensions

sgagliardi — Sat, 14 Aug 2021 03:34:27 GMT

I came up with the following solution on the dimension, that seems to work well:

=aggr(only({<[Patient Encounter]=P([Patient Encounter])>}if(aggr(rank(-[Patient Encounter]),[Patient ID],[Patient Encounter])=1,[Attending Physician Name])),[Patient ID])