IF Statement really bugging me

[#1]

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

[jpapador]

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)

[#3]

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!

S

[rajeshvaswani77]

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

[#5]

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!

[richard · Accepted solution]

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'))

[#7]

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.

S