Set Analysis on Expression on Bar Chart

[#1]

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.

[#2]

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]),'##.##%')

[#3]

Try changing the " to a single quote '

[jonathandienst]

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

[#5]

Thanks for your replies.

Zack, the error message was removed with your expression but the resulting chart returned all treatment function codes not just the ones identifed in the expression.

Jonathan, that sounds like a very good idea! What is the basic script I would need to use to create a variable pleases?

Thanks

[jonathandienst]

Lee

I cannot paste into the forum from where I am entering this post, so I have appended a sample script in a qvs file. In case you are confused, chr(39) is the code for a single quote character, as the treatment codes need to be wrapped in single or double quotes.

Hope this helps

Jonathan