Divide an expression by the total of the expression

[qliklizzy]

Hi

i have an expression called Extra which diplays a count of the difference between beds used and allocated by specialty

i need to be able to divide the extra for each specialty by the total extra to work out the probability for each specialty.

which i though i could just do [Extra] / sum([Extra] but this does not work.

in excel i have the workings below

I have left the formula in C26 to show the proability workings needed

once this is calculated i need to work out how many beds to allocate based on the specialties probabilty.

the formula is the same for each specialty apart from ELM - the if statement refers to 1/3 rather than 1/6

is this doable in an expession ?

thanks liz

24	A	B	C	D
25	Spec	Extra number	Probabilty	allocate
26	CARD	5	C26/$C$31	IF(B26>="1/6",0,((1/6-B26)*A32))
27	DIAB/ENDOC	-4	-  4/9	IF(C27>=1/6,0,((1/6-C27)*C31))
28	ELM	9	1	IF(C28>=1/3,0,((1/3-C28)*C31))
29	GASTRO	-1	-  1/9	IF(C29>=1/6,0,((1/6-C29)*C31))
30	RESPMED	0	0	IF(C30>=1/6,0,((1/6-C30)*C31))
31	Total	9

[sunny_talwar]

My bad, try this

Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Correct'}>} LocalPatientID) + Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Outlier'}>} LocalPatientID)

- if(RevisedSpec='DIAB/ENDOC', 21,

if(CurrentSpec='CARD', 18,

if(CurrentSpec='ELM', 52,

if(CurrentSpec='GASTRO', 28,

if(CurrentSpec='RESPMED', 26, 0)))))

[qliklizzy]

yes it does match

[sunny_talwar]

Name of the dimension?

Is it CurrentSpec or RevisedSpec or Spec?

[qliklizzy]

revised spec, which is just current spec but groups two of my specs together.

so it doesnt matter about there other spec fields now, revised spec will cover all

[sunny_talwar]

Try this

Probablity

Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Correct'}>} LocalPatientID) + Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Outlier'}>} LocalPatientID)

- if(RevisedSpec='DIAB/ENDOC', 21,

if(CurrentSpec='CARD', 18,

if(CurrentSpec='ELM', 52,

if(CurrentSpec='GASTRO', 28,

if(CurrentSpec='RESPMED', 26, 0)))))

/Sum(TOTAL Aggr(

Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Correct'}>} LocalPatientID) + Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Outlier'}>} LocalPatientID)

- if(RevisedSpec='DIAB/ENDOC', 21,

if(CurrentSpec='CARD', 18,

if(CurrentSpec='ELM', 52,

if(CurrentSpec='GASTRO', 28,

if(CurrentSpec='RESPMED', 26, 0)))))

, [RevisedSpec]))

Allocate

If(Spec = 'ELM',

     If(

Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Correct'}>} LocalPatientID) + Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Outlier'}>} LocalPatientID)

- if(RevisedSpec='DIAB/ENDOC', 21,

if(CurrentSpec='CARD', 18,

if(CurrentSpec='ELM', 52,

if(CurrentSpec='GASTRO', 28,

if(CurrentSpec='RESPMED', 26, 0)))))

/Sum(TOTAL Aggr(

Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Correct'}>} LocalPatientID) + Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Outlier'}>} LocalPatientID)

- if(RevisedSpec='DIAB/ENDOC', 21,

if(CurrentSpec='CARD', 18,

if(CurrentSpec='ELM', 52,

if(CurrentSpec='GASTRO', 28,

if(CurrentSpec='RESPMED', 26, 0)))))

, [RevisedSpec]))

>= (1/3), 0, (((1/3) - (

Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Correct'}>} LocalPatientID) + Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Outlier'}>} LocalPatientID)

- if(RevisedSpec='DIAB/ENDOC', 21,

if(CurrentSpec='CARD', 18,

if(CurrentSpec='ELM', 52,

if(CurrentSpec='GASTRO', 28,

if(CurrentSpec='RESPMED', 26, 0)))))

/Sum(TOTAL Aggr(

Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Correct'}>} LocalPatientID) + Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Outlier'}>} LocalPatientID)

- if(RevisedSpec='DIAB/ENDOC', 21,

if(CurrentSpec='CARD', 18,

if(CurrentSpec='ELM', 52,

if(CurrentSpec='GASTRO', 28,

if(CurrentSpec='RESPMED', 26, 0)))))

, [RevisedSpec]))

)) *

Sum(TOTAL Aggr(

Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Correct'}>} LocalPatientID) + Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Outlier'}>} LocalPatientID)

- if(RevisedSpec='DIAB/ENDOC', 21,

if(CurrentSpec='CARD', 18,

if(CurrentSpec='ELM', 52,

if(CurrentSpec='GASTRO', 28,

if(CurrentSpec='RESPMED', 26, 0)))))

, [RevisedSpec]))

)),

     If(

Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Correct'}>} LocalPatientID) + Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Outlier'}>} LocalPatientID)

- if(RevisedSpec='DIAB/ENDOC', 21,

if(CurrentSpec='CARD', 18,

if(CurrentSpec='ELM', 52,

if(CurrentSpec='GASTRO', 28,

if(CurrentSpec='RESPMED', 26, 0)))))

/Sum(TOTAL Aggr(

Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Correct'}>} LocalPatientID) + Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Outlier'}>} LocalPatientID)

- if(RevisedSpec='DIAB/ENDOC', 21,

if(CurrentSpec='CARD', 18,

if(CurrentSpec='ELM', 52,

if(CurrentSpec='GASTRO', 28,

if(CurrentSpec='RESPMED', 26, 0)))))

, [RevisedSpec]))

>= (1/6), 0, (((1/6) - (

Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Correct'}>} LocalPatientID) + Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Outlier'}>} LocalPatientID)

- if(RevisedSpec='DIAB/ENDOC', 21,

if(CurrentSpec='CARD', 18,

if(CurrentSpec='ELM', 52,

if(CurrentSpec='GASTRO', 28,

if(CurrentSpec='RESPMED', 26, 0)))))

/Sum(TOTAL Aggr(

Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Correct'}>} LocalPatientID) + Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Outlier'}>} LocalPatientID)

- if(RevisedSpec='DIAB/ENDOC', 21,

if(CurrentSpec='CARD', 18,

if(CurrentSpec='ELM', 52,

if(CurrentSpec='GASTRO', 28,

if(CurrentSpec='RESPMED', 26, 0)))))

, [RevisedSpec]))

)) *

Sum(TOTAL Aggr(

Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Correct'}>} LocalPatientID) + Count({<ConsOutlier = {'Cons_Urgent'}, IPwards = {'InpatientWards'}, CorrectWard *= {'Outlier'}>} LocalPatientID)

- if(RevisedSpec='DIAB/ENDOC', 21,

if(CurrentSpec='CARD', 18,

if(CurrentSpec='ELM', 52,

if(CurrentSpec='GASTRO', 28,

if(CurrentSpec='RESPMED', 26, 0)))))

, [RevisedSpec]))

)))

[qliklizzy]

expression works for probabilty but shows 21.8 for CARD not 5/9

and the other one doesnt work im afraid

i cant belive how long these expression are >

[sunny_talwar]

Difficult to say without getting a chance to peek at what you have. Also, I might have accidentally made a mistake in the expressions.... Would you be able to share a sample to check this out?

[qliklizzy]

hi

thanks so much

i have attached a copy version

[sunny_talwar · Accepted solution]

See if the attached is what you wanted

[qliklizzy]

yes that is brilliant,

although all the other specialties have come through, only those i had listed before with right ward / wrong ward counts are needed.

also the allocate doesnt display minus beds ? just 0

thanks for al your help