Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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?

Tags (2)
5 Replies

Re: Want to create a filter based on a calculated expression

Hi

Try like this

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

Hope it helps

Not applicable

Re: Want to create a filter based on a calculated expression

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

Re: Want to create a filter based on a calculated expression

HI

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

Not applicable

Re: Want to create a filter based on a calculated expression

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

Re: Want to create a filter based on a calculated expression

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

Community Browser