topic Re: Want to create a filter based on a calculated expression in QlikView

Want to create a filter based on a calculated expression

Thu, 18 Oct 2012 18:11:47 GMT

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?

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

MayilVahanan — Thu, 18 Oct 2012 18:21:40 GMT

Try like this

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

Hope it helps

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

Thu, 18 Oct 2012 18:44:15 GMT

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

MayilVahanan — Thu, 18 Oct 2012 18:46:25 GMT

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

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

Thu, 18 Oct 2012 19:41:42 GMT

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.

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

Fri, 19 Oct 2012 18:57:26 GMT

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