topic Re: Count all possible from alternate state in App Development

Count all possible from alternate state

sgagliardi — Thu, 28 May 2020 05:42:17 GMT

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.

Re: Count all possible from alternate state

tresB — Thu, 28 May 2020 06:15:42 GMT

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.

Re: Count all possible from alternate state

lironbaram — Thu, 28 May 2020 06:11:29 GMT

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

Re: Count all possible from alternate state

sgagliardi — Thu, 28 May 2020 14:30:09 GMT

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.

Re: Count all possible from alternate state

tresB — Thu, 28 May 2020 15:38:51 GMT

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

Re: Count all possible from alternate state

sgagliardi — Thu, 28 May 2020 21:40:38 GMT

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.

Re: Count all possible from alternate state

tresB — Fri, 29 May 2020 05:34:43 GMT

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.

Re: Count all possible from alternate state

sgagliardi — Fri, 29 May 2020 06:06:32 GMT

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!

Re: Count all possible from alternate state

tresB — Fri, 29 May 2020 10:07:58 GMT

I would wait for the sample app.

Re: Count all possible from alternate state

sgagliardi — Mon, 01 Jun 2020 18:23:30 GMT

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.

Re: Count all possible from alternate state

sgagliardi — Tue, 09 Jun 2020 04:37:17 GMT

I finally figured it out. If anyone is interested, below is my solution:

=count({1<[Patient ID]=p({<[Patient ID]>}),[Admit Date]={"$(= '>=' & date($(DateofIndex)+$(ContinuumB)) & '<=' & date($(DateofIndex)+$(ContinuumA)))"}>} distinct [Encounter Number])

//$(DateofIndex)=aggr(if(rank(num([Admit Date]))=1,[Admit Date]),[Encounter Number])
//$(ContinuumA)= User Input
//$(ContinuumB)= User Input

Re: Count all possible from alternate state

tresB — Tue, 09 Jun 2020 05:35:49 GMT

Great! So the date range condition finally within the count(). 🙂

Re: Count all possible from alternate state

sgagliardi — Wed, 10 Jun 2020 18:16:28 GMT

Hey @tresB

Yes! I credit you with the solution on this one for providing me the inspiration to approach it from another angle.

I feel like I've ALMOST got it down, but now I am struggling with the rank() function I'm using in the one variable to grab earliest occurring admit date for the selected encounter numbers for each patient number. What the code is doing now is grabbing earliest admit date for the full selection, and not for each patient number. As a result, the date range build in set analysis considerably skews the count. Stumped again.

aggr(if(rank([Admit Date])=1,[Admit Date]),[Encounter Number])

Re: Count all possible from alternate state

tresB — Thu, 11 Jun 2020 07:39:55 GMT

May be include the patient dimension as well in aggr(), like:

aggr(if(rank([Admit Date])=1,[Admit Date]),[Patient ID],[Encounter Number])

Re: Count all possible from alternate state

sgagliardi — Fri, 12 Jun 2020 22:02:45 GMT

Hi @tresB ,

Thanks for the suggestion! This returns for me the full sum or total, seemingly disregarding the date range that's being established in the set analysis.

I wonder however if perhaps this is creating the behavior I'm after, but instead counting encounters with admit dates occurring in any of the ranges being dynamically created for each MRN in the selection...

Still pecking away at it, this is proving to be a tricky problem.