2 Replies Latest reply: Feb 17, 2011 5:03 AM by thompsons RSS

    Help with strange result using the AGGR function?

      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

        • Help with strange result using the AGGR function?
          Vlad Gutkovsky

          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,

            • Help with strange result using the AGGR function?

              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!