Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

laurischarf
Contributor II

Patients without a certain type of test

In Sense Enterprise, I'm trying to create a dimension to allow the user to select Patients who did not have a certain test performed in the past year.

This is akin to the common need to find customers without orders (see Filter for customers without orders‌ as an example). The small difference is that I need to identify Patients who did not have any A1C tests. They could well have had other tests.

The Patient table contains a list of patients.

The Observation table contains the tests.

PatientID is a key field in both tables.


I have tried creating the dimension using Aggr but it shows nothing in the filter pane:

=Aggr(If(Sum({<PatientID= ,ObservationName={'HEMOGLOBIN A1C'}, ObservationDate={">=$(=AddYears(Today(), -1))<Today()"}>} [Observation.Counter])>0, 'Yes', 'No'), PatientID)

Visit.Counter is 1 for each row in the Observation table.

Thank you for any tips.

1 Solution

Accepted Solutions
luismadriz
Valued Contributor

Re: Patients without a certain type of test

Hi Lauri,

I could be mistaken but I don't think you'll be able to get it that way. I think I'd do a straight table with patient names where there would be one measure with a set analysis that satisfies your requirements. For example:

Count({<PatientID=e({<TestType={"A1C"}>} PatientID)>} TestID)

Please see in the image below that the table at the bottom lists Patients that never had one test

Untitled.png

I hope this helps,

Cheers,

Luis

PS. When applicable please mark the appropriate replies as Correct. This will help community members know which discussions have already been addressed and have a possible known solution. Please mark replies as Helpful if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as Helpful if you feel additional info is useful to others

5 Replies
MVP
MVP

Re: Patients without a certain type of test

It is not necessary with an advanced aggregation - a set expression should do.

If you have PatientID as a dimension in a table then this could be the measure:

Only(

  {

    1<ObservationDate={">=$(=AddYears(Today(),-1))<Today()"}>

    -

    <ObservationName={'HEMOGLOBIN A1C'},ObservationDate={">=$(=AddYears(Today(),-1))<Today()"}>

  }

  PatientName

)

Assumption: there is no row for any test that hasn't been done in the observation table.

You can't have PatientID as the field since that is the key-field. Use another field in the patient table.

luismadriz
Valued Contributor

Re: Patients without a certain type of test

Hi Lauri,

I could be mistaken but I don't think you'll be able to get it that way. I think I'd do a straight table with patient names where there would be one measure with a set analysis that satisfies your requirements. For example:

Count({<PatientID=e({<TestType={"A1C"}>} PatientID)>} TestID)

Please see in the image below that the table at the bottom lists Patients that never had one test

Untitled.png

I hope this helps,

Cheers,

Luis

PS. When applicable please mark the appropriate replies as Correct. This will help community members know which discussions have already been addressed and have a possible known solution. Please mark replies as Helpful if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as Helpful if you feel additional info is useful to others

luismadriz
Valued Contributor

Re: Patients without a certain type of test

Thanks Petter,

One keeps learning everyday!

I just tried Only and it works beautifully as a measure to add to a table

=Only({1-<TestType={"A1C"}>} PatientName)

When I tried it as a dimension, it wouldn't recognise it

Cheers,

Luis

laurischarf
Contributor II

Re: Patients without a certain type of test

Thank you both, Petter and Luis!

Luis' solution is simpler, but both get the desired result in the straight table listing the patients.

I'd still like to create a Filter Pane dimension -- it would be Yes/No to select the patients who did/didn't have a test in the past year. I could create the dimension during the Load, as long as I reload the data early every morning so that Today() is accurate. Is that the best option?

luismadriz
Valued Contributor

Re: Patients without a certain type of test

Hi Lauri,

I think that could work nicely.

I imagine you'd add that flag when loading the Patients table and looking or peeking on the transactions table for that test during that period.

I haven't done it and not sure how it would work but it's a nice exercise. If you want you could create another post with some sample data. Just keep it as simple as possible, in that way other people more knowledgeable than me might comment.

Cheers,

Luis