Fractile of an expression

[#1]

Hello community!

I'm using Qlikview to analyse some data on the performance of a group of care homes over the last 50 months.  One of the key metrics I need to calculate and chart over time is the occupancy rate, which is done using the "Occupied beds" and "Effective beds" data fields as follows:

Sum([Occupied beds]) / Sum([Effective beds])

So far, so simple.

What I need to do next is chart the 25th and 75th percentiles of occupancy over time.  I've been using the following but getting no results:

Fractile (Sum([Occupied beds]) / Sum([Effective beds]), 0.75)

I think the issue is caused because the fractile function won't work when the value you want the percentile for is the result of an expression itself.

Is there a way of working around this to create the charts I need?  I'm pretty new to Qlikview so could be missing something really obvious.

Thanks in advance

John

[jvitantonio]

Create a variable with your expression and then use the variable inside the fractile function

[#3]

Thanks for the tip.  I thought that might be the answer.  I'm currently searching around qlikview trying to remember how to create variables.  Could you point me in the right direction?

Thanks

John

[jonathandienst]

John

The problem is that Fractile is an aggregate statement and you cannot nest another aggregate statement (sum).

You could use something like:

Fractile (Aggr(Sum([Occupied beds]) / Sum([Effective beds]), HomeID), 0.75)

Where HomeID is the dimension on which you want to get the fractiles (change as appropriate for your application)

Regards

Jonathan

[#5]

Thanks Jonathan, I'm massively grateful for your help and am now almost there I think.

My data is monthly but this is an annual chart.  The syntax you suggested seems to work but the results only appear in my chart for the first year of the timeline (2008).  Do you have any idea why this may be happening?

[jvitantonio]

Try with

Fractile (Aggr(Distinct Sum([Occupied beds]) / Sum([Effective beds]), HomeID), 0.75)

or

Fractile (Aggr(Nodistinct Sum([Occupied beds]) / Sum([Effective beds]), HomeID), 0.75)

[#7]

Interesting.  Distinct has no impact.  Nodistinct gives me the same result across the timeline (I'm pretty sure it should vary by year).  Out of interest, what do the distinct and nodistinct commands do?

[jvitantonio · Accepted solution]

REad the help

Listen, did you try adding Year to the aggregating function? Next to HomeID?

Otherwise post your application to we cna work on it

[#9]

Thanks, read the help, I can see why nodistinct might have worked.  I'll keep trying.

Unfortunately I can't share my file.  If it helps, the relevant data fields are as follows:

HomeID: Unique ID for each home in my sample

Month: Monthly timeline from Jan 08 to Dec 11

Year: Year from the above timeline

Effective beds: The number of beds in the home each month (very rarely changes)

Occupied beds: The number of beds occupied by patients in each home each month (changes but never exceeds effective beds)

To recap, I'd like a chart of the 75th percentile of % occupation by home over time with an annual timeline.

Once again, massively grateful for the help so far.

[#10]

Just used the following with success!

Fractile (aggr(nodistinct sum([Occupied beds])/sum([Effective beds]),[Site ref], [Year]),0.75)

Thanks!