percentage calculation in script problem

[rcorcoran]

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

];

[simenkg]

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

[rcorcoran]

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

[simenkg]

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)