Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
qliklizzy
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

   

24ABCD
25SpecExtra numberProbabilty allocate
26CARD5C26/$C$31IF(B26>="1/6",0,((1/6-B26)*A32))
27DIAB/ENDOC-44/9 IF(C27>=1/6,0,((1/6-C27)*C31))
28ELM91      IF(C28>=1/3,0,((1/3-C28)*C31))
29GASTRO-11/9 IF(C29>=1/6,0,((1/6-C29)*C31))
30RESPMED00      IF(C30>=1/6,0,((1/6-C30)*C31))
31Total9
1 Solution

Accepted Solutions

Re: Divide an expression by the total of the expression

See if the attached is what you wanted

21 Replies

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

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

Re: Divide an expression by the total of the expression

What is the expression behind Total and Allocated?

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

Re: Divide an expression by the total of the expression

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

qliklizzy
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

qliklizzy
Contributor

Re: Divide an expression by the total of the expression

so the table i am after fro example ..

   

SpecRight WardWrong WardTotalAllocated bedsExtra numberProportionALLOCATE
CARD20323185  5/9 -4
DIAB/ENDOC1161721-44/9 6
ELM4714615291      0
GASTRO2252728-11/9 3
RESPMED197262600      2
Total13736173 9

   

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?

qliklizzy
Contributor

Re: Divide an expression by the total of the expression

it says error in set modifier expression ?

thanks