I am trying to count how many patients have a total hospital spell cost greater that a certain amount. When I use the following calculation I always get an answer which is 1 higher than expected! Does anyone know why and also how to fix the problem?
Example: Max Cost = 50,000
In a text box (whilst testing the calc out) I have =sum(if(aggr(sum(SpellCost),Patientid)>=MaxCost,1,0))
I also have a pivot table showing 'patientid', 'sum(spellcost)', Yes/No coming from the calc if(aggr(sum(SpellCost),Patientid)>=MaxCost,'Yes','No')
The pivot table shows me 5 different patients who have a total cost >=50000 but the text box comes back with the answer 6 ??
Typically you would want to do an aggr(if( and not an if(aggr(. It is helpful to think of an aggr as creating a straight table in memory, where the expression is your first parameter and the dimensions are your 2+ parameters. So, change your first expression to this:
The piviot table expression givesthe same results as I had,which is fine. But the first expression still gives one more than expected (after I took 'nodistinct' out - this gave me hundreds more which may be something to do with blank patientids?)
If I put 'distinct' into my original first expression it gives the correct number but I don't know why, so am not really happy about just putting it in. Also, if I choose a month in one of the pages selection box,es, it goes back to giving 1 more than the answer?!
I may have to give up and just use the pivot table. (I was going to use the first expression in a graph to show the number of high cost patients by GP practice)