topic Set Analysis on Expression on Bar Chart in QlikView

Set Analysis on Expression on Bar Chart

Wed, 05 Oct 2011 08:54:30 GMT

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

Wed, 05 Oct 2011 18:57:34 GMT

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

Wed, 05 Oct 2011 19:03:31 GMT

Try changing the " to a single quote '

Set Analysis on Expression on Bar Chart

jonathandienst — Wed, 05 Oct 2011 19:24:41 GMT

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

Set Analysis on Expression on Bar Chart

Thu, 06 Oct 2011 08:44:50 GMT

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

Re: Set Analysis on Expression on Bar Chart

jonathandienst — Thu, 06 Oct 2011 10:38:42 GMT

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