topic Re: Possible Values Excluded From Count in App Development

Possible Values Excluded From Count

sgagliardi — Fri, 29 May 2020 19:23:23 GMT

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

Re: Possible Values Excluded From Count

jwjackso — Fri, 29 May 2020 21:30:36 GMT

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

Re: Possible Values Excluded From Count

sgagliardi — Mon, 01 Jun 2020 12:25:23 GMT

Hi Jwjackso,

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

Re: Possible Values Excluded From Count

sunny_talwar — Mon, 01 Jun 2020 13:28:15 GMT

May be this?

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

Re: Possible Values Excluded From Count

tresB — Mon, 01 Jun 2020 13:35:12 GMT

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/1706358#M54154

Re: Possible Values Excluded From Count

sgagliardi — Mon, 01 Jun 2020 21:13:33 GMT

Hey Sunny,

Thank you for your help. As @tresB 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.

Re: Possible Values Excluded From Count

sgagliardi — Wed, 03 Jun 2020 21:01:32 GMT

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.

Re: Possible Values Excluded From Count

sgagliardi — Tue, 09 Jun 2020 04:40:44 GMT

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