percentage calculation in script problem

rcorcoran — Tue, 12 Aug 2014 08:41:35 GMT

I am trying to calculate a percentage in the script for Percentage Readmission and have put in the following:

LEFT JOIN (EWTD)

LOAD

TLMKey,

SLMKey,

MetricKey,

ActualValue,

if((TLMKey=18 and SLMKey=200) and ((MetricKey=5) or (MetricKey=6)), (sum(MetricKey=5)*100/MetricKey=6)) as PercentageReadmission,

if((TLMKey=18 and SLMKey=205) and ((MetricKey=3) or (MetricKey=4)) ,(sum((MetricKey=3) and MetricKey=4))) as sumOutpatients

The SumOutpatients calculation works fine but I cannot get the PercentageReadmission to work. Any help would be appreciated. Below is the full script in case it helps.

thanks

Rhona

RESIDENT EWTD

GROUP BY TLMKey,SLMKey,MetricKey,ActualValue

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='€#,##0.00;-€#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

OLEDB CONNECT32 TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Healthstat;Data Source=cifdevelopment;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=EL6470B0001;Use Encryption for Data=False;Tag with column collation when possible=False];

//-------- Start Multiple Select Statements ------

EWTD:

LOAD ActualValue,

TLMKey,

SLMKey,

MetricDesc,

MetricKey,

//Acute hospital metrics

if((TLMKey=18 and SLMKey=142 and MetricKey=3) or (TLMKey=18 and SLMKey=142 and MetricKey=5) or (TLMKey=18 and SLMKey=142 and MetricKey=4),MetricKey) as Discharges,

if((TLMKey=18 and SLMKey=205 and MetricKey=3) or (TLMKey=18 and SLMKey=205 and MetricKey=4),MetricKey) as Outpatients,

if(TLMKey=18 and SLMKey=166 and MetricKey=4,MetricKey) as [Reducing Health Care acquired Infection],

if((TLMKey = 18 and SLMKey = 200 and MetricKey=5) or (TLMKey = 18 and SLMKey = 200 and MetricKey=6),MetricKey) as [Rates of Readmission],

//Primary Care Metrics

if(TLMKey = 10 and SLMKey = 103 and (MetricKey>=1 or MetricKey <=4),MetricKey) as [Community Intervention Team by Source],

if(TLMKey=10 and SLMKey=192 and MetricKey=20,MetricKey) as [Physio Wait >12 wks],

if(TLMKey = 10 and SLMKey = 191 and (MetricKey>=68 or MetricKey <=79),MetricKey) as [OccTherapy wait> 16 weeks],

if((TLMKey=10 and SLMKey=101 and MetricKey=1) or (TLMKey=10 and SLMKey=101 and MetricKey=2) or (TLMKey=10 and SLMKey=101 and MetricKey=11) or(TLMKey=18 and SLMKey=142 and MetricKey=12),MetricKey) as [Ortho No Wait 12 Mths],

if((TLMKey=10 and SLMKey=101 and MetricKey=1) or (TLMKey=10 and SLMKey=101 and MetricKey=2),MetricKey) as [Ortho Wait >4 yrs],

if((TLMKey=10 and SLMKey = 101 and (MetricKey>=1 or MetricKey <=4) or (TLMKey=10 and SLMKey = 101 and (MetricKey>=5 or MetricKey <=16))),MetricKey) as Orthodontics,

RDODesc,

RDOKey,

ReferredMM,

ReferredYYYY,

SGKey,

SGName,

ServiceProvider,

ServiceProviderType,

ServiceProviderNum,

if(ServiceProviderType='H',ServiceProvider) as Hospital,

if(ServiceProviderType='T',ServiceProvider) as HospitalGroup,

if(ServiceProviderType='O',ServiceProvider) as Team

;

SQL SELECT *

FROM Healthstat.dbo."MMI_ScorecardLevel3"

where (tlmkey = 18 and slmkey = 142 and MetricKey=3) or (tlmkey = 18 and slmkey = 142 and MetricKey=4)or(tlmkey = 18 and slmkey = 142 and MetricKey=5)or (tlmkey = 18 and slmkey = 166 and MetricKey=4) or (tlmkey = 18 and slmkey = 200 and MetricKey=5 ) or (tlmkey = 10 and slmkey = 192 and MetricKey=20 )or (tlmkey = 18 and slmkey = 200 and MetricKey=6)or

(tlmkey = 10 and slmkey = 103 and (MetricKey>=1 or MetricKey <=4))or (tlmkey = 10 and slmkey = 191 and (MetricKey>=68 or MetricKey <=79))or (tlmkey = 10 and slmkey = 101 and (MetricKey>=1 or MetricKey <=2)) or (tlmkey = 10 and slmkey = 101 and (MetricKey>=11 or MetricKey <=12)) or (tlmkey = 10 and slmkey = 101 and MetricKey=10) or (tlmkey = 10 and slmkey = 101 and MetricKey=16)

or (tlmkey=10 and slmkey = 101 and (MetricKey>=1 or MetricKey <=4)) or (tlmkey=10 and slmkey = 101 and (MetricKey>=5 or MetricKey<=16))

or (tlmkey=18 and slmkey = 205 and (MetricKey>=3 or MetricKey <=4))

and (ServiceProviderType='H' or ServiceProviderType='T' or ServiceProviderType='O')

;

LEFT JOIN (EWTD)

LOAD

TLMKey,

SLMKey,

MetricKey,

ActualValue,

if((TLMKey=18 and SLMKey=200) and ((MetricKey=5) or (MetricKey=6)), (sum(MetricKey=5)*100/MetricKey=6)) as PercentageReadmission,

if((TLMKey=18 and SLMKey=205) and ((MetricKey=3) or (MetricKey=4)) ,(sum((MetricKey=3) and MetricKey=4))) as sumOutpatients

RESIDENT EWTD

GROUP BY TLMKey,SLMKey,MetricKey,ActualValue

;

MonthMapping:

LOAD * INLINE [

ReferredMM, Month

1, Jan

2, Feb

3, Mar

4, Apr

5, May

6, June

7, July

8, Aug

9, Sept

10, Oct

11, Nov

12, Dec

];

Re: percentage calculation in script problem

simenkg — Tue, 12 Aug 2014 08:51:46 GMT

load *,

MetricKey5/MetricKey6 as PercentageReadmission;
LOAD

TLMKey,

SLMKey,

MetricKey,

ActualValue,

if((TLMKey=18 and SLMKey=200) and MetricKey=5, (sum(MetricKey)*100)) as MetricKey5,

if((TLMKey=18 and SLMKey=200) and MetricKey=6, (sum(MetricKey))) as MetricKey6,

if((TLMKey=18 and SLMKey=205) and (MetricKey=3 or MetricKey=4) ,sum(MetricKey)) as sumOutpatients

Re: percentage calculation in script problem

rcorcoran — Tue, 12 Aug 2014 10:46:28 GMT

Thanks Simen

But I cannot get it to work. I am now trying to do it as a dimension in

the pivot table as follows:

=If(=5, 'readmissions 5', If([Rates of

Readmission]=6, 'Discharges 6',if(aggr(sum([Rates of

Readmission]=5*100/[Rates of Readmission]=6),ActualValue) ,'Percentage

Readmission',Null())))

If you had a minute could you have a look. Basically I want the pivot

table to show the dimensions:

Readmissions 5

Discharges 6

Percentage Readmission - which is the percentage calculation

I'm really stuck

Thanks

Rhona Corcoran

ICT Services

Re: percentage calculation in script problem

simenkg — Tue, 12 Aug 2014 11:59:07 GMT

In the expression-tab use the following expressions and change the label to match the names you want for them.

Readmissions:

sum({$<[Rates of Readmission]={5}>} ActualValue)

Discharges

sum({$<[Rates of Readmission] = {6}>} ActualValue)

and

Percentage Readmission:

sum({$<[Rates of Readmission]={5}>} ActualValue) / sum({$<[Rates of Readmission] = {6}>} ActualValue)

topic Re: percentage calculation in script problem in QlikView

percentage calculation in script problem

Re: percentage calculation in script problem

Re: percentage calculation in script problem

Re: percentage calculation in script problem