topic Re: if condition in QlikView

if condition

surajap123 — Thu, 26 Jun 2014 12:25:37 GMT

Hi All,

In my existing if condition, i want to display only field(ID) value greater than 10. Below is the expression i am using and i want to modify it to show values >10.

count(if(

(FromDate >= Weekstart and FromDate <= Weekend) or

(ToDate >= Weekstart and ToDate <= Weekend) or

(FromDate <= Weekstart and ToDate >= Weekstart) or

(FromDate <= Weekend and ToDate >= Weekend) , ID))

Re: if condition

Gysbert_Wassenaar — Thu, 26 Jun 2014 12:27:55 GMT

Try

count({<ID={'>10'}>} if(

(FromDate >= Weekstart and FromDate <= Weekend) or

(ToDate >= Weekstart and ToDate <= Weekend) or

(FromDate <= Weekstart and ToDate >= Weekstart) or

(FromDate <= Weekend and ToDate >= Weekend) , ID))

Re: if condition

its_anandrjs — Thu, 26 Jun 2014 12:31:55 GMT

Or you can write like

count(if( ID > 10 and

(FromDate >= Weekstart and FromDate <= Weekend) or

(ToDate >= Weekstart and ToDate <= Weekend) or

(FromDate <= Weekstart and ToDate >= Weekstart) or

(FromDate <= Weekend and ToDate >= Weekend) , ID))

Re: if condition

surajap123 — Thu, 26 Jun 2014 14:05:44 GMT

Hi Gysbert,

Your expression is still showing me the ID's <10 in the chart. Could you please use my app attached .

Re: if condition

its_anandrjs — Thu, 26 Jun 2014 14:32:00 GMT

It is working suraj try to add the ID field in the dimension and export the data as excel file and then you check the ID it is not available there. The ID that you get is only

OutPut:-

ID
25
24
12
16
19
23
27
15

Re: if condition

surajap123 — Thu, 26 Jun 2014 15:55:32 GMT

Hi Anand,

ID is a measure for me. I want to show only the ID values that are greater than 10 for each week.

If you could provide me the expression in the my app, that would be really helpful..

Re: Re: if condition

Thu, 26 Jun 2014 16:53:43 GMT

Hi,

why don't you use a calculated dimenssion with ID:

if(ID>10,ID,null()) and supress the null values.

Re: if condition

eduardo_sommer — Thu, 26 Jun 2014 17:24:52 GMT

Hi anand

I would only add a pair of parenthesis to your expression:

count(if( ID > 10 and (

(FromDate >= Weekstart and FromDate <= Weekend) or

(ToDate >= Weekstart and ToDate <= Weekend) or

(FromDate <= Weekstart and ToDate >= Weekstart) or

(FromDate <= Weekend and ToDate >= Weekend) ), ID))

Eduardo

Re: if condition

its_anandrjs — Thu, 26 Jun 2014 18:45:07 GMT

It depends on your condition and there are many ways to doing this and depends how represent the data.

1. First Way is add calculated dimensions

Dimension1:- if(ID>10,Weekstart)

Dimension2:- if(ID>10,Weekend)

Expression:-

count(if(

((FromDate >= Weekstart and FromDate <= Weekend) or

(ToDate >= Weekstart and ToDate <= Weekend) or

(FromDate <= Weekstart and ToDate >= Weekstart) or

(FromDate <= Weekend and ToDate >= Weekend)) , ID))

Note:- Suppress when value is null selected in dimension properties and in presentation properties also

2. Second way is

Dimension1:- Weekstart

Dimension2:- Weekend

Expression:-

Count(if( ID >10 And (

((FromDate >= Weekstart and FromDate <= Weekend) or

(ToDate >= Weekstart and ToDate <= Weekend) or

(FromDate <= Weekstart and ToDate >= Weekstart) or

(FromDate <= Weekend and ToDate >= Weekend)) ) , ID))

Let me know if data not match.

Re: if condition

surajap123 — Tue, 01 Jul 2014 08:40:51 GMT

Hi All,

Thanks everyone for your time and inputs.

To give the background of my business requirement, I am counting total number of people who was present in each week. Here each ID field indicates one person and the below expression counts IDs that are falling in a week.

count(if(

(FromDate >= Weekstart and FromDate <= Weekend) or

(ToDate >= Weekstart and ToDate <= Weekend) or

(FromDate <= Weekstart and ToDate >= Weekstart) or

(FromDate <= Weekend and ToDate >= Weekend) , ID))

I want to show the row in straight table when the ID count is >10 in a week.

I have used all the expressions provided by you all, but they still i can see expression column having values <10.

You guys gave me suggestion to use ID>10 in the calculation dimension, but ID's are always unique value of one person, so i dont know how it works

Regards,

Suraj

Re: if condition

SunilChauhan — Tue, 01 Jul 2014 08:48:31 GMT

try this in expression

if(count(if(

(FromDate >= Weekstart and FromDate <= Weekend) or

(ToDate >= Weekstart and ToDate <= Weekend) or

(FromDate <= Weekstart and ToDate >= Weekstart) or

(FromDate <= Weekend and ToDate >= Weekend) , ID))>10,

count(if(

(FromDate >= Weekstart and FromDate <= Weekend) or

(ToDate >= Weekstart and ToDate <= Weekend) or

(FromDate <= Weekstart and ToDate >= Weekstart) or

(FromDate <= Weekend and ToDate >= Weekend) , ID)))

Re: if condition

surajap123 — Tue, 01 Jul 2014 09:39:23 GMT

Hi Sunil,

This expression is working. I will test it in my main dashboard and come back by evening. Thanks !!

Re: if condition

surajap123 — Wed, 02 Jul 2014 14:48:45 GMT

Thanks everyone for your support !!