topic How to write expression based on condition? in QlikView

How to write expression based on condition?

Tue, 24 Jun 2014 16:04:47 GMT

Hi,

I have to write an expression in pivot table.

Dimension1: Label

Dimension2: Topper

Expression: Count(ID)

but the expression should need the condition as

If Label='Poppy' and Topper equal to any other value instead of Topper<>'Poppy' then the expression should show count(Id). how can I write the expression.

can anyone suggest me please.

Thanks.

Re: How to write expression based on condition?

Clever_Anjos — Tue, 24 Jun 2014 16:09:18 GMT

count(if(Label='Poppy' and Topper <> 'Poppy',Id)

Re: How to write expression based on condition?

Gysbert_Wassenaar — Tue, 24 Jun 2014 16:10:57 GMT

count({< Label = {'Poppy'},Topper -= {'Poppy'} >}ID)

Re: How to write expression based on condition?

Wed, 25 Jun 2014 02:42:11 GMT

count({$< Label = {'Poppy'},Topper -= {'Poppy'} >}ID)

Re: How to write expression based on condition?

sarvjeet — Wed, 25 Jun 2014 03:30:06 GMT

You can achieve this in front end using expression:

count({< Label = {'Poppy'},Topper -= {'Poppy'} >} ID)

you can handle this in back end. Create another field in datamodel: Col1 with definition

if(Label='Poppy' and Topper <> 'Poppy',1,0)

In UI use expression Sum(Col1)

Aggregation is faster as compare to count with set analysis

-Sarvjeet

Re: How to write expression based on condition?

Wed, 25 Jun 2014 10:19:16 GMT

Thanks.

I have a condition where Label='ert','tyu','iop','pou','rew','ion','Tr' and Topper ='Divi','patch','Copy','po' then the expression should show count(Id). will below expression work for it?

Dimension1: Label

Dimension2: Topper

Expression: Count({<Label={'ert','tyu','iop','pou','rew','ion','Tr'}

,Topper ={'Divi','patch','Copy','po'}>} ID)

Re: How to write expression based on condition?

PradeepReddy — Wed, 25 Jun 2014 10:44:04 GMT

try the below thing

Count({<Label={'Poppy'},Topper ={'*'}-{'Poppy'}>} ID)

Re: How to write expression based on condition?

Wed, 25 Jun 2014 10:58:48 GMT

Thanks.

I have a condition where Label='ert','tyu','iop','pou','rew','ion','Tr' and Topper ='Divi','patch','Copy','po' then the expression should show count(Id). will below expression work for it?

Dimension1: Label

Dimension2: Topper

Expression: Count({<Label={'ert','tyu','iop','pou','rew','ion','Tr'}

,Topper ={'Divi','patch','Copy','po'}>} ID)

Re: How to write expression based on condition?

PradeepReddy — Wed, 25 Jun 2014 11:32:45 GMT

IF Your requirement is..

Counting the no.of ID's with Label='ert' 'tyu','iop','pou','rew','ion','Tr' and Topper ='Divi','patch','Copy','po'.

then whatever expression your writing is correct.

Re: How to write expression based on condition?

Wed, 25 Jun 2014 14:51:10 GMT

Thanks.

Re: How to write expression based on condition?

robert99 — Wed, 25 Jun 2014 16:44:33 GMT

"Aggregation is faster as compare to count with set analysis"

Are you sure. I would always use count. Even count distinct in preference to sum based on a blog post by Henric

http://community.qlik.com/blogs/qlikviewdesignblog/2013/10/22/a-myth-about-countdistinct

Does using set analysis make a difference. But even so I would still use count distinct.