topic Re: IF Statement really bugging me in QlikView

IF Statement really bugging me

Mon, 26 Aug 2013 14:41:37 GMT

Community,

I appreciate any assistance with this IF statement that has been bugging me for a while now. I'm pretty new to this piece of QV and thank you in advance for any guidance.

IF ([Order Status]='Open' and [Order Date] <=Today-7,'0-7 Days', IF [Order Date] >Today-8<15, '8-14 Days', IF [Order Date]>Today-15<22,'15-21 Days', IF [Order Date] >Today-21 '22+Days')

Cheers!

Sharon

Re: IF Statement really bugging me

jpapador — Mon, 26 Aug 2013 14:45:27 GMT

If you are doing this in a script you have to close all of the if statements and name your field. Also I would just nest the if statement again and not use the AND at the beginning. For example:

IF ([Order Status]='Open',

IF([Order Date] <=Today-7,'0-7 Days',

IF ([Order Date] >Today-8<15, '8-14 Days',

IF ([Order Date]>Today-15<22,'15-21 Days',

IF ([Order Date] >Today-21 '22+Days'))))) as OrderDateBuckets

Every IF has it's own set of parenthesis at the beginning and then they all close at the end.

Also you syntax for the today command should be Today()-7, You may even want to put it in parenthesis like so (Today()-7)

Re: IF Statement really bugging me

Mon, 26 Aug 2013 14:46:33 GMT

Hi,

Can you plese post your sample file

Re: IF Statement really bugging me

rajeshvaswani77 — Mon, 26 Aug 2013 14:47:15 GMT

You are almost there

IF ([Order Status]='Open' and [Order Date] <=(Today()-7),'0-7 Days', IF([Order Date] >(Today()-8<15), '8-14 Days', IF( [Order Date]>(Today()-15)<22,'15-21 Days', IF( [Order Date] >(Today()-21), '22+Days'))))

Braces for if if stetement and added () to Today().

Thanks,

Rajesh Vaswani

Re: IF Statement really bugging me

jagan — Mon, 26 Aug 2013 14:53:32 GMT

Hi,

Try this, missed brackets for Today

IF ([Order Status]='Open',

IF([Order Date] <=Today()-7,'0-7 Days',

IF ([Order Date] >Today()-8 AND [Order Date]<Today()-15, '8-14 Days',

IF ([Order Date]>Today()-15 AND [Order Date]<Today()-22,'15-21 Days',

IF ([Order Date] >Today()-21, '22+Days'))))) as OrderDateBuckets

Re: IF Statement really bugging me

Mon, 26 Aug 2013 15:01:25 GMT

jpapdor,

I appreciate your help and guidance in re: to the ()'s. I did add them after Today and additionally put the Today-7 in parens; sadly I am still receiving an error in my expression. Am still trying to tweak and with your assistance feel I am much closer. Thank you!

Re: IF Statement really bugging me

Mon, 26 Aug 2013 15:02:12 GMT

Amuthabharathi,

I am unable to post a sample file due to confidentiality. I'm sorry. Thanks for your willingness to assist!

Re: IF Statement really bugging me

jpapador — Mon, 26 Aug 2013 15:03:25 GMT

Are you trying to do this in an expression in a chart or in the script editor?

Re: IF Statement really bugging me

Mon, 26 Aug 2013 15:03:32 GMT

Rajesh,

This was definitely helpful but am still getting an error in the expression. I think with your help and the other comments here that I am much closer. i'll continue to work with it and let you know what works in the end. Thanks so much!

Re: IF Statement really bugging me

Mon, 26 Aug 2013 15:04:42 GMT

Jpapdor,

i am adding this into an expression for a chart.

Re: IF Statement really bugging me

jpapador — Mon, 26 Aug 2013 15:12:11 GMT

I would suggest making this anew field with the script editor. Essentially you are trying to make a new dimension (the different buckets of time) while that is possible to do directly in the chart it will be faster and easier to manage to create it in the script. Once you create the new field you can create a chart with your new buckets field as the dimension and then the expression would be Count(OrderDate). This would give you what you are looking for.

If you wanted to this directly in chart you need to make that if statement a Calculated dimension:

IF ([Order Status]='Open',

IF([Order Date] <=Today()-7,'0-7 Days',

IF ([Order Date] >Today()-8 AND [Order Date]<Today()-15, '8-14 Days',

IF ([Order Date]>Today()-15 AND [Order Date]<Today()-22,'15-21 Days',

IF ([Order Date] >Today()-21, '22+Days')))))

And then the expression would be Count(OrderDate)

Re: IF Statement really bugging me

Mon, 26 Aug 2013 15:14:55 GMT

This does sound much easier and was my first instinct. I appreciate the redirection. I'll keep you posted on how that works.

Thanks for all of your help!

Re: IF Statement really bugging me

rajeshvaswani77 — Mon, 26 Aug 2013 15:18:38 GMT

Hi Saharon,

If you go and keep teh cursor near the bracket in if then it will turn red and the the closing bracket will also turn red.

we are nesting all the if conditions

it is basically

if(condition,then value,if(then value, else value))

Any QlikView function that does not need any arguments must end with ()

Press f1 key and refer the help file that comes with the QlikView desktop edition.

thanks,

Rajesh Vaswani

Re: IF Statement really bugging me

Mon, 26 Aug 2013 15:23:02 GMT

Thank you Rajesh.

I'll keep working on it and let you know what I find. It looks correct but I'm still too new to this side of QV to see something out of place. I even copied and pasted your recommendation and still received an error. There's probably something else I have missed so I'll keep digging.

thank you!

Re: IF Statement really bugging me

richard — Mon, 26 Aug 2013 15:23:28 GMT

Try this. I used two intersections because your comparison is based on two fields: order status and order date.

IF([Order Status]='Open',
IF([Order Date] <= (Today()-7),'0-7 Days',
IF ([Order Date] > (Today()-8) AND [Order Date]<(Today()-15), '8-14 Days',
IF ([Order Date]>(Today()-15) AND [Order Date]<(Today()-22),'15-21 Days'))),
IF ([Order Date] >Today()-21,'22+Days'))

Re: IF Statement really bugging me

Mon, 26 Aug 2013 15:27:35 GMT

Richard,

Thanks so much! this worked.

It really makes sense now because I am relying on two different dimensions.

Thanks for being willing to help out the newbie. This one was really getting to me.