Announcements
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## 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

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

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
Specialist II

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

MVP

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

Community Browser