topic Re. :If statement issue in QlikView

If statement issue

renjithpl — Mon, 31 Jan 2011 10:02:23 GMT

Hi All,

Look at the below formula, its not giving an answer, Please help me in finding the error,

=(9 /(count(if(Stage='S1', DocID))*2)) + (7/(count(if(Stage='S2', DocID))*3))

THe condition is, when stage =S1 the count of DocID * 2 and the result should be divided with 9

and when Stage=S2 the count of DocID * 3 and the result should be divided with 7.

Regards

Ren

Re. :If statement issue

martin59 — Mon, 31 Jan 2011 10:13:50 GMT

Hello,

You can try this :

=9 /(count({<Stage={S1}>} DocID)*2) + 7/(count({<Stage={S2}>} DocID)*3)

Hope that helps you

If statement issue

jonathandienst — Mon, 31 Jan 2011 11:05:48 GMT

Ranjit

Your description and formula are not the same. I understand that may be just language issue - but in case not...

The problem may be due to the division - if one of the counts is zero, you will be attempting to divide by zero, which is undefined and therefore no answer. Perhaps your formula should be:

=count(if(Stage='S1', DocID))*2/9 + count(if(Stage='S2', DocID))*3/7

You could also convert it to a set expression as in Martin's post - unless this is in a chart/table with Stage as one of the dimensions.

Jonathan

If statement issue

renjithpl — Tue, 01 Feb 2011 06:56:09 GMT

Hi Jonathan,

thanks for the reply, i have used Martins answer and its working fine, but now there is a change in my formula.

Can you help me in figuring out this issue,

my previous post says (9/ (count({<Stage={S1}>} DocID)*2)) + (7/(count({<Stage={S2}>} DocID)*3)

but now i want some thing like this, (9+7) / Count(if(Stage='S1', DocID)*2, if(Stage='S2', DocID)*3)

Below is my scenario if you dont understand my question.

in S1 i have 100 docs with 2 probabilities, in S2 i have 100 docs with 3 probabilities. My whole idea is to calculate the error rate in such stages.

so in Stage1 total number of error is 9, therefore ------------------ 9/(Count of DocID *Probability)

in Stage2 total number of error is 7, therefore ------------------- 7/(Count of DocID*Probability)

I have a list box Stages, it has S1 and S2.

if i select S1 qlikview should calculate - 9/(100*2)

if i select S2 qlikview should calculate - 7/(100*3)

if i select S1&S1 qlikview shoudl calculate - (9+7)/(200+300)

Please help me in this scenario.

Re. :If statement issue

renjithpl — Tue, 01 Feb 2011 07:01:57 GMT

Hi Martin,

your answer is working fine, but there is a new issue, which i am not able to solve,

my previous post says (9/ (count({<Stage={S1}>} DocID)*2)) + (7/(count({<Stage={S2}>} DocID)*3)

but now i want some thing like this, (9+7) / Count(if(Stage='S1', DocID)*2, if(Stage='S2', DocID)*3)

Below is my scenario if you dont understand my question.

In S1 i have 100 docs with 2 probabilities, in S2 i have 100 docs with 3 probabilities. My whole idea is to calculate the error rate in such stages.

so in Stage1 total number of error is 9, therefore ------------------ 9/(Count of DocID *Probability)

In Stage2 total number of error is 7, therefore ------------------- 7/(Count of DocID*Probability)

I have a list box Stages, it has S1 and S2.

If i select S1 qlikview should calculate - 9/(100*2)

If i select S2 qlikview should calculate - 7/(100*3)

If i select S1&S1 qlikview shoudl calculate - (9+7)/(200+300)

Please help me in this scenario