# New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Contributor

## Divide an expression by the total of the expression

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
1 Solution

Accepted Solutions
MVP

## Re: Divide an expression by the total of the expression

See if the attached is what you wanted

21 Replies
MVP

## Re: Divide an expression by the total of the expression

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

Contributor

## Re: Divide an expression by the total of the expression

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

MVP

## Re: Divide an expression by the total of the expression

What is the expression behind Total and Allocated?

Contributor

## Re: Divide an expression by the total of the expression

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)

MVP

## Re: Divide an expression by the total of the expression

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

Contributor

## Re: Divide an expression by the total of the expression

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

Contributor

## Re: Divide an expression by the total of the expression

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

MVP

## Re: Divide an expression by the total of the expression

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?

Contributor

## Re: Divide an expression by the total of the expression

it says error in set modifier expression ?

thanks