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]

Don't know what your expression is for Extra number, but assuming it is Sum([Extra number]), try this

Probablity

Sum([Extra number])/Sum(TOTAL [Extra number])

Allocate

If(Spec = 'ELM',

     If(Sum([Extra number])/Sum(TOTAL [Extra number]) >= (1/3), 0, (((1/3) - (Sum([Extra number])/Sum(TOTAL [Extra number]))) * Sum(TOTAL [Extra number]))),

     If(Sum([Extra number])/Sum(TOTAL [Extra number]) >= (1/6), 0, (((1/6) - (Sum([Extra number])/Sum(TOTAL [Extra number]))) * Sum(TOTAL [Extra number]))))

[qliklizzy]

Hi

my expressions is : [Total]-[Allocated]

but these two fields are expressions also if that matters ?

i tried:

Sum([Total]-[Allocated])/Sum(TOTAL [Total]-[Allocated])

[sunny_talwar]

What is the expression behind Total and Allocated?

[qliklizzy]

allocated:

if(RevisedSpec='DIAB/ENDOC',21,
if(CurrentSpec='CARD', 18,
if(CurrentSpec='ELM', 52,
if(CurrentSpec='GASTRO', 28,
if(CurrentSpec='RESPMED', 26, 0)))))

total: =[Right Ward]+[Wrong Ward]

right ward:    =count(if(ConsOutlier='Cons_Urgent' and IPwards='InpatientWards' and CorrectWard='Correct',LocalPatientID))

conditional: if(ConsOutlier='Cons_Urgent' and IPwards='InpatientWards' and CorrectWard='Correct, 1)

wrong ward: =count(if(ConsOutlier='Cons_Urgent' and IPwards='InpatientWards' and CorrectWard='Outlier',LocalPatientID))

conditional if(ConsOutlier='Cons_Urgent' and IPwards='InpatientWards' and CorrectWard='Outlier', 1)

[sunny_talwar]

What do you mean when you say Conditional? The expressions have conditional hide/show?

[qliklizzy]

yes the report is all based on outliers but i had to then show the counts of those in the right bed so i made the espression conditional

[qliklizzy]

so the table i am after fro example ..

Spec	Right Ward	Wrong Ward	Total	Allocated beds	Extra number	Proportion	ALLOCATE
CARD	20	3	23	18	5	5/9	-4
DIAB/ENDOC	11	6	17	21	-4	-  4/9	6
ELM	47	14	61	52	9	1	0
GASTRO	22	5	27	28	-1	-  1/9	3
RESPMED	19	7	26	26	0	0	2
Total	137	36	173		9

[sunny_talwar]

Can you check if the below expression give you the same number as Extra Number?

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)))))

Can you also tell the name of your dimension? Is it CurrentSpec or RevisedSpec or Spec?

[qliklizzy]

it says error in set modifier expression ?

thanks