topic Re: If greater than and less than expression in QlikView

If greater than and less than expression

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Anil_Babu_Samineni — Wed, 25 Oct 2017 15:47:08 GMT

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), .....

Ricardo_Gerhard — Wed, 25 Oct 2017 16:04:52 GMT

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.

vvira1316 — Wed, 25 Oct 2017 16:34:34 GMT

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

MarcoWedel — Wed, 25 Oct 2017 20:33:50 GMT

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