Possible Values Excluded From Count

[sgagliardi]

Hello Qlik Community,

I have a scenario where I am attempting to reference possible values relative to a selection in the default state. I want to return a count of possible [Encounter Numbers] relative to [Patient ID] included in my selection. The expression below gets me pretty close, but I've found that it entirely excludes [Encounter Numbers] pertaining to [Patient ID] that occur more than once in my selection. I cannot for the life of me figure it out.

aggr(nodistinct only({1<[Encounter Number]=p({<[Encounter Number]>})>}[Encounter Number]),[Patient ID])

[jwjackso]

Doesn't the Only  function return a Null if there are more than 1 instances?

[sgagliardi]

Hi Jwjackso, 

That was my understanding of it also, but replacing only() with sum() here returns me a similar result.

[sunny_talwar]

May be this?

Aggr(NODISTINCT 
    Count({1<[Encounter Number] = p({<[Encounter Number]>})>}[Encounter Number])
, [Patient ID])

[tresesco · Accepted solution]

Hi @sgagliardi ,

I would add your previous thread on the same issue assuming that would give others here a better understanding. https://community.qlik.com/t5/Qlik-Sense-App-Development/Count-all-possible-from-alternate-state/m-p...

[sgagliardi]

Hey Sunny,

Thank you for your help. As @tresesco has mentioned, I have a separate a separate thread pertaining to a similar issue. At the time I was thinking about them separately, but  have since recognized them to really be the same issue. On that thread I have since included a model app and more info about what I am trying to do.

Your suggestion is helpful, it gets me the count that I'm after when modified to be:

Aggr(NODISTINCT 
    Count({1<[Patient ID] = p({<[Patient ID]>})>}[Encounter Number])
, [Patient ID])

The other piece of this problem comes from returning this count in relation to a date range established by a user. For the [Patient ID](s) with [Encounter Number](s) included in a data selection, there is an [Admit Date] field that I am leveraging so that if a user set 30 days before, and 60 days after, a range is created wherein all other possible [Encounter Numbers] related to that [Patient ID] with an [Admit Date] occurring occurring in between the established date range, are counted. Attached is the model app I had provided in my other thread. Find that in cases where two [Patient IDs] (with separate [Encounter Numbers]) occur in the same data selection, the "Continuum" KPI fails to count encounters related to that [Patient ID]. Possibly because it is not sure which [Encounter Number] [Admit Date] to use.

[sgagliardi]

Still no luck here. With further testing and having taken many more stabs at it, I've concluded my problem definitely has to do with how the [Patient Number]'s [Admit Date] is identified for cases where there are two or more [Encounter Number]s. I've been playing around with Min() and FirstSortedValue() on the [Admit Date] dimension, but with no luck as of yet.

[sgagliardi]

Finally figured it out, solution added to other thread:

https://community.qlik.com/t5/Qlik-Sense-App-Development/Count-all-possible-from-alternate-state/m-p...