Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
danschofield1
New Contributor

If greater than and less than expression

Hi

Im trying to do an expression where if the calculation of two fields is greater than x and less than y then give me z as an answer....

Basically I want to do something like

if((EmployeeLeftDate - EmployeeStartDate)/30.4) >0<3,"0-3",

if((EmployeeLeftDate - EmployeeStartDate)/30.4) >3<6,"3-6",

if((EmployeeLeftDate - EmployeeStartDate)/30.4) >6<12,"6-12",

if((EmployeeLeftDate - EmployeeStartDate)/30.4) >12<36,"12-36","36+"

Im very new to Qlikview and don't really understand set analysis yet im assuming I need to use that to achieve this?

In excel I would simple do

If(And(ELD-ESD>0,ELD-ESD<3),"0-3",If(And(ELD-ESD>3,ELD-ESD<6),"3-6",.............. and so on

How do I go about achieving this in a qlikview expression??

Thanks

Dan

4 Replies

Re: If greater than and less than expression

This?

If([ELD-ESD] >0 and [ELD-ESD] <3, Dual('0-3',1),

If([ELD-ESD] >3 and [ELD-ESD] <6, Dual('3-6',2), .....

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Employee
Employee

Re: If greater than and less than expression

Dear Daniel,

   You can use your rule on load script, creating a new field.

   On the interface you could create table object with sum(newfield) to do this calc, and using any kind of field as dimension.

Ricardo Gerhard
OEM Solution Architect
LATAM
vvira1316
Valued Contributor II

Re: If greater than and less than expression

Hi,

What Anil has recommended should work. Can you please explain what you are trying to do with following inf your IF statements

(EmployeeLeftDate - EmployeeStartDate)/30.4


If you could attach a sample data and app then will be easier to help you.


BR,

Vijay

Re: If greater than and less than expression

Hi,

instead of the queer excel syntax


If(And(ELD-ESD>0,ELD-ESD<3),"0-3",If(And(ELD-ESD>3,ELD-ESD<6),"3-6",.............. and so on

you would use something like this in QlikView:


If(ELD-ESD>0 and ELD-ESD<3,'0-3',If(ELD-ESD>3 and ELD-ESD<6,'3-6',.............. and so on


or shorter


If(ELD-ESD>0, If(ELD-ESD<3,'0-3',If(ELD-ESD<6,'3-6',.............. and so on

hope this helps

regards

Marco