Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Want to create a filter based on a calculated expression

I have a table that is counting the number of visits, prescriptions and readmitions for each patient.  So the column will read

Patient     |     Visits     |     Scripts     |     Readmits

John Doe  |     10          |     4             |     1

I want to be able to create filters that would allow me to limit my data to members with:

  • >= 5 Visits
  • >=10 Scripts
  • >= 2 Readmits
  • etc

When I try to enter a formula into the selection box it gets grayed out and doesn't work

if(sum({<CostService={'Primary Care'}>} Current_Util)>4,'Yes','No')

Any thoughts?

5 Replies
MayilVahanan

Hi

Try like this

Aggr(if(sum({<CostService={'Primary Care'}>} Current_Util)>4,'Yes','No'),Current_Util)

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

This seems to give me a 'Yes' whenever there is more than 1, but not necessarily the threshold that I want to set, >4

MayilVahanan

HI

Can you post a sample file? and say your requirement in detail.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

If I'm looking at record level entries and not aggregations in a table, then I would create an association table called:

VisitBuckets:

Visit, VisitBucket1,VisitBucket2

1, Less than 2, Less than 5

2, Exactly 2 , Less than 5

3, Greater than 2, Less than 5

4, Greater than 2, Less than 5

5, Greater than 2, Exactly 5

6, Greater than 2, Greater than 5

..

1000,Greater than 2, Greater than 5

Now you have an associated dimension(s) that you can use for grouping.

Do the same with your other facts that need groups.

This also allows for the work to be done in the load script and reduces in memory processing in production.

Not applicable
Author

I'm not familiar with Association tables, but I'm not exactly sure how that works when you are calculating fields rather than using existing fields in the data.  So in order to get visits I am summing Current_Util only when CostService = 'Primary Care'. So I'm not sure how you would associate both of those fields as you have described.

I think I over simplified the expressions because each category is a calculation like the one I just mentioned. And then I'm summing the risk flags to get an overall risk level.

(Ex: Readmits = count({$<VISIT_TYPE={'IP'},READMIT_90DAYS={'1'}>} DISTINCT VISITNUMBER))

Risk Level = if(visits > 5,1,0) + if(Scripts > 10,1,0) + if(Readmits > 2,1,0) [0=Low, 1=Medium, >1=High]

And I want a filter for each risk and the overall risk level