5 Replies Latest reply: Oct 6, 2011 6:38 AM by Jonathan Dienst RSS

    Set Analysis on Expression on Bar Chart

      Hi

       

      We want to produce a bar chart of contribution percentage by treatment function code however due to the way some of our data is processed with get a lot of dummy treatment function codes that we don't want to chart.

       

      I've done a bar chart with the dimension as Treatment Function Code and the expression I want to use is as follows (which works if I have a single field - say revenue- but I need the calculated "Contribution percentage" as per the latter part of the expression. Any ideas how I can get this to work please as it just says Error in Expression.

       

      =SUM({$<[Treatment Function Code]={
      "100-GENERAL SURGERY",
      "101-UROLOGY",
      "110-TRAUMA & ORTHOPAEDICS",
      "120-ENT",
      "130-OPHTHALMOLOGY",
      "140-ORAL SURGERY",
      "143-ORTHODONTICS",
      "160-PLASTIC SURGERY",
      "170-CARDIOTHORACIC SURGERY",
      "180-ACCIDENT & EMERGENCY",
      "191-PAIN MANAGEMENT",
      "300-GENERAL MEDICINE",
      "301-GASTROENTEROLOGY",
      "302-ENDOCRINOLOGY",
      "303-HAEMATOLOGY (CLINICAL)",
      "320-CARDIOLOGY",
      "330-DERMATOLOGY",
      "340-THORACIC MEDICINE",
      "361-NEPHROLOGY",
      "370-MEDICAL ONCOLOGY",
      "400-NEUROLOGY",
      "410-RHEUMATOLOGY",
      "420-PAEDIATRICS",
      "430-CARE OF THE ELDERLY",
      "430S-STROKE SERVICES",
      "501-OBSTETRICS FOR PATIENTS USING A HOSPITAL BED OR",
      "502-GYNAECOLOGY",
      "560-MIDWIFE EPISODE",
      "650-PHYSIOTHERAPY",
      "654-DIETETICS",
      "822-CHEMICAL PATHOLOGY",
      "960-NURSE LED"
      }>}NUM((SUM([revenue]) - (SUM([COST_NURSING])+SUM([COST_CONSULTANTS])+SUM([COST_OTHERCLINICALPAY])+SUM([COST_NONCLINICALPAY])
      +SUM([COST_DRUGS])+SUM([COST_CLINICALSUPPLIES])+SUM([COST_OTHERDIRECTCOSTS])+SUM([COST_ALLIEDHEALTHPROFESSIONALS])+SUM([COST_RADIOLOGY])+SUM([COST_PATHOLOGY])
      +SUM([COST_THEATRECOSTS])+SUM([COST_OTHERSERVICES])+SUM([COST_PROSTHETICS])+SUM([COST_HOTELSERVICES])+SUM([COST_PHARMACY]))) / SUM([revenue]),'##.##%')))
      
      


      Any help appreciated. Thanks.

        • Set Analysis on Expression on Bar Chart

          try this:

           

          =NUM((SUM({$<[Treatment Function Code]={

          "100-GENERAL SURGERY",

          "101-UROLOGY",

          "110-TRAUMA & ORTHOPAEDICS",

          "120-ENT",

          "130-OPHTHALMOLOGY",

          "140-ORAL SURGERY",

          "143-ORTHODONTICS",

          "160-PLASTIC SURGERY",

          "170-CARDIOTHORACIC SURGERY",

          "180-ACCIDENT & EMERGENCY",

          "191-PAIN MANAGEMENT",

          "300-GENERAL MEDICINE",

          "301-GASTROENTEROLOGY",

          "302-ENDOCRINOLOGY",

          "303-HAEMATOLOGY (CLINICAL)",

          "320-CARDIOLOGY",

          "330-DERMATOLOGY",

          "340-THORACIC MEDICINE",

          "361-NEPHROLOGY",

          "370-MEDICAL ONCOLOGY",

          "400-NEUROLOGY",

          "410-RHEUMATOLOGY",

          "420-PAEDIATRICS",

          "430-CARE OF THE ELDERLY",

          "430S-STROKE SERVICES",

          "501-OBSTETRICS FOR PATIENTS USING A HOSPITAL BED OR",

          "502-GYNAECOLOGY",

          "560-MIDWIFE EPISODE",

          "650-PHYSIOTHERAPY",

          "654-DIETETICS",

          "822-CHEMICAL PATHOLOGY",

          "960-NURSE LED"

          }>}[revenue]) -

          (SUM([COST_NURSING])+SUM([COST_CONSULTANTS])+SUM([COST_OTHERCLINICALPAY])+SUM([COST_NONCLINICALPAY])

          +SUM([COST_DRUGS])+SUM([COST_CLINICALSUPPLIES])+SUM([COST_OTHERDIRECTCOSTS])+SUM([COST_ALLIEDHEALTHPROFESSIONALS])+SUM([COST_RADIOLOGY])+SUM([COST_PATHOLOGY])

          +SUM([COST_THEATRECOSTS])+SUM([COST_OTHERSERVICES])+SUM([COST_PROSTHETICS])+SUM([COST_HOTELSERVICES])+SUM([COST_PHARMACY]))) / SUM([revenue]),'##.##%')

          • Set Analysis on Expression on Bar Chart

            Try changing the " to a single quote '

            • Set Analysis on Expression on Bar Chart
              Jonathan Dienst

              Hi

               

              I see an extra closing bracket at the end of the statemement. Try removing that.

               

              Some more general suggestions, more about optimisation than fixing the problem:

               

              ...I would concatenate all the treatment codes into a variable in the load script and use that variable:

               

              {<[Treatment Function Code]={$(vTreatmentCodes)}>}

               

              ... and I would combine some the the cost elements into a combined cost field during load (you can leave the individual ones there if you need them elsewhere in your model)

               

              ... and finally since sum(a) + sum(b) = sum(a + b), I would use the latter form:

               

              SUM([COST_NURSING] +  [COST_CONSULTANTS] + [COST_OTHERCLINICALPAY] + ...)

               

              Regards

              Jonathan