Count all possible from alternate state

[sgagliardi]

Hey Qlik Sense Community, reaching out because I am at the end of my rope with a particular problem.

In a KPI object in an alternate state, I am attempting to count all possible encounter numbers of patient numbers that are currently selected in the default state. What I have written works great when the selections I have made in the default state are 1-to-1  with the patient number, but when there are multiple instances of the same patient number, Qlik Sense throws out all countable encounters, returning 0 for that count. Here is what I have written:

count(distinct aggr(nodistinct only({$<[Encounter Number]=P([Encounter Number])>}[Encounter Number]),[Patient Number]))))

Imagine the table looks something like:

[Patient Number]	[Encounter Number]	[Diagnosis]
1357	1234	470
2468	5678	871
5790	9101	470
1256	1213	470
2367	1415	871
1357	1617	470
2675	1819	234

I have selected in the default state the Diagnosis 470. I would expect my KPI to return 4, though it returns 2, as patient number 1357 occurs twice. When I select Diagnosis 871, I return 2, and I select Diagnosis 234, I return 1.

Any thoughts here would be super helpful. I've played around with this for a couple of weeks, but this is as close as I've gotten. I suspect maybe there is a conflict of some sort around the distinct/nodistinct usage, but I have not  been able to figure it out.

[tresesco]

I don't see an alternate state (except the default) in your expression that confuses me about how it is relevant here. Also would request you to explain a bit more about your requirement comparing how it would be different if you use a simpler expression like Count([Encounter Number]) , may be with an example (in the light of different selections).

Perhaps there could be a simpler expression.

[lironbaram]

your expression return 2 because your aggr function try to build a table with one line for each patient

so for patient 1357 the only function returns null , 
if you want to count patients than replace your formula to this  

count(distinct aggr(nodistinct only({$<[Encounter Number]=P([Encounter Number])>}[Patient Number]),[Patient Number]))

if you want to count encounters than use this one 

count(distinct aggr(nodistinct only({$<[Encounter Number]=P([Encounter Number])>}[Encounter Number]),[Encounter Number],[Patient Number]))

[sgagliardi]

Hey Tresesco,

Thank you for your reply.

The alternate state is set on the object itself in settings, and is necessary for separating it from the default state. I apologize for not explaining better, and I also left out some important information from my example.

Imagine the table looks something like:

[Patient Number]	[Encounter Number]	[Diagnosis]
1357	1234	470
2468	5678	871
5790	9101	470
1256	1213	470
2367	1415	871
1357	1617	470
1357	2456	871
2675	1819	234

I've added an additional row here, one with a patient number of 1357, and diagnosis of 871. I have selected in the default state the Diagnosis 470. I would now expect my KPI to return 5, though it returns 3. What should be included in the count is all instances of Diagnosis 470, plus a count of all excluded encounter numbers that share a patient number with those rows containing 470. I would want the instance of 1357 with diagnosis 871 to be included in the count also. This is the reason I am using the alternate state, as we are essentially making selections over top of the default selections.

When I select Diagnosis 871, I return 5, and I select Diagnosis 234, I return 1, as these diagnosis have a one-t-one match of the diagnosis to the patient number, whereas Diagnosis 470 has a duplicate instance of patient number 1357.

I hope that this makes what I'm attempting to do a little more clear.

[tresesco]

I guess you need:

=Count({1<[Patient Number]=p({<[Patient Number]>})>}[Encounter Number])

You need distinct or not...you can decide based on your data possibility

[sgagliardi]

Tresesco, again, thank you so much for your help.

I've been working with the syntax you provided earlier. It works great independent of the alternate state I was using. I'd like to escape using the alternate state on objects if possible, but I've run into a more complex problem as a result.

What I was doing was counting the values described in response to user inputs passed with a variable to count only encounter numbers falling within a date range relative to the date of the selected encounter(s). I was referencing the date of the selected encounter number in the default state, and establishing the date range to be plus and minus however many days the user inputs. What I would count is the encounters that fall within that range that also share a selected patient number. This is what I have so for, no alternate state applied to the KPI object:

=count(distinct {1<[Patient Number]=p({<[Patient Number]>})>}
if(
num(aggr(nodistinct only({1<[Patient Number]=p({<[Patient Number]>})>}[Admit Date]),[Encounter number]))-num([Admit Date])
<=$(DaysAfter)
and
num(aggr(nodistinct only({1<[Patient Number]=p({<[Patient Number]>})>}[Admit Date]),[Encounter Number]))-num([Admit Date])
>=$(DaysBefore),
[Encounter Number])
)

I think my problem might be how I'm referencing the selected encounter numbers. I'm getting the full count I would expect from the expression you provided, but I can't get the KPI to respond to the user inputs as it was before.

[tresesco]

I wouldn't say that I have understood all you are trying to do, however, it seems that you need not put the date range condition outside the main count() but inside making the expression simpler. My suggestion to you would be to create a sample app where we can see what/how you are trying to do - may be, we would be able to play with the same sample app and come up with something meaningful in terms of a possible solution.

Also please try to explain the expected output against multiple controls (like selections and inputs) in the context of same sample app.

[sgagliardi]

Hi Tresesco,

By bringing the date range condition inside of the main count(), do you mean converting it to a set analysis of the count()?

What I am trying to make happen is the syntax you had shared earlier:

count(distinct {1<[Patient Number]=p({<[Patient Number]>})>}[Encounter Number])

But do this within a particular date range that is determined based upon an input. The expression I had shared earlier does this fine (though it could probably be simplified), but for instances where a [Patient Number] occurs twice within a selection, it exclude the whole [Patient Number], dropping all of it's possible [Encounter Numbers] from the count.

I will see about trying to build an example app that I can share. The data I work with is big, sensitive in nature, and the app I'm working on is proprietary. I'll need to model some data and and Qlik objects with similar qualities that describe my scenario.

Again, really appreciate you help with this!

[tresesco]

I would wait for the sample app.

[sgagliardi]

Hey Tresesco,

Thanks again for your help.

Attached is a model for what I am attempting to achieve.

I've loaded some dummy data which includes [Patient Number], [Encounter Number], [Diagnosis Code], and [Admit Date].

In the attached app you'll fine 2 KPI objects, a variable input selector, selection filters, and an index table.

What we are trying to do is get a count of all encounter numbers associated with patient numbers captured in a data selection, with admit dates occurring between however many days before, and however may days after (as input by the user) the admit date of the selected patient number's index encounter number(s). In this example I am using diagnosis code as my selection criteria, but this could be any criteria.

To replicate the behavior I've described, select a diagnosis code (pretty much any one). You'll find that in most cases this is a 1 to 1 match, with the diagnosis criteria usually having 1 encounter number, and 1 patient number counted in the Index KPI. The continuum KPI will return a count of all other encounter numbers associated with the selected patient number(s) that occur between the date range created by the user's inputs relative to the admit date of the selected index encounter for that MRN.

Now, select Diagnosis Code 'M457'. Find that the Index KPI reflects 2 encounter numbers, and 1 patient, indicating that the same patient has two encounter numbers that occur within the diagnosis selection. Find in the continuum KPI, that the count returns 0 encounters for this patient number, while we would expect a count of several encounter numbers.

I believe that this has to do with the expression not identifying which of the two available encounter numbers of the patient ID selected in the index criteria to take the admit date from. I have yet to be able to figure out how to solve this.