Patients without a certain type of test

[Lauri]

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.

[petter]

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 · Accepted solution]

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

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]

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

[Lauri]

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]

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