Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

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.

5 Replies
Not applicable

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

Not applicable

Set Analysis on Expression on Bar Chart

Try changing the " to a single quote '

MVP
MVP

Set Analysis on Expression on Bar Chart

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Set Analysis on Expression on Bar Chart

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

MVP
MVP

Re: Set Analysis on Expression on Bar Chart

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein