Help with strange result using the AGGR function?

[#1]

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 ??

Any ideas why this is happening?

Thanks

Sandra Thompson

[vgutkovsky]

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:

sum(aggr(nodistinct if(sum(SpellCost)>=MaxCost,1,0),Patientid))

Change your last pivot table expression to this: if(sum(SpellCost)>=MaxCost,text('Yes'),text('No'))

Hopefully that will match up, but let me know if it still doesn't work.
Cheers,

[#3]

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)

Thanks for helping!