Set analysis if statement date and variable

[QlikBeginner1]

Hi,

I have a variable 

vTodayDateT = Date(Today(),'DD/MM/YYYY');

vTodayDateYS = YearStart(Date(Today(),'DD/MM/YYYY'));

and i have created an if statement 

=if((([Quote Created Date] <= $(vTodayDateT)) AND ([Quote Created Date]>= $(vTodayDateYS))),1,0)

The dates are producing correctly if you look at the bottom of the screenshot, The Quote Created Date is also in Date Format.

,date([quote_created_date_key], 'DD/MM/YYYY') as [Quote Created Date]

However for some reason, the flag doesnt work? It just retreives a '0' value? please can someone explain why this isnt working?

[Andrei_Cusnir · Accepted solution]

Hello,

I believe that the issue is with how the variables are declared. Here are my observations:

1. If you isolate the parts of the if statement you will notice:

As you can see the [Quote Created Date] <= $(vTodayDateT) part is not being properly calculated as it always returns 0, hence the response is always 0.

2. Now if you declare the variables as let they will appear as:

3. But if you declare them as set, they will appear as: 

4. The outcome with declaring them as set is:

As you can see the calculations are properly evaluated on runtime, which gives you the right result.

I have removed some () from you code and the final one I used is:

=If(
 (
   [Quote Created Date] <= $(vTodayDateT)
   AND 
   [Quote Created Date] >= $(vTodayDateYS)
 ),
 1,
 0
)

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 
 

[QlikBeginner1]

Thank you Andrei. 

I did initally try the SET and LET on the variables, I believed because the text turned black, it was incorrect. I have tried and now can see the flags are working! Do you think I should SET all my variables ? 

Thanks for your help so quickly! Really appreciate it 

[Andrei_Cusnir · Accepted solution]

Hello,

I am glad to hear that you were able to resolve the issue. Regarding your latest question, I believe that is up to your use case scenario, so in case you see that the outcome is not the right one, you can test the other option for that variable. It mainly has to do with how the data is stored in the variable. If you use LET then it will first evaluate the expression and then store the value, but if you use SET, it will store the entire expression as the variable, so when you are going to use it, it will evaluate the expression on run time. 

One simple example is:

Imagine that you want a variable to have the current date. So here are the 2 options:

LET vToday = Today(); Will get the value 15/06/2022, but this value will not be updated unless you reload the data again. So if you want to use the variable vToday on the next day, it will still have the old date until you reload the data.

However:

SET vToday = Today(); Will keep the expression stored, so when you use the variable, it will get evaluated on runtime, which means that every time you will have the correct current date, without the need to reload the data.

One last tip:

When you build complex statements like If(CONDITION1 AND CONDITION2 ...) and you see that the outcome is not the expected one, use in Straight table the CONDITION1 and CONDITION2 as separate measures to test if those 2 return the desired values. This will allow you to troubleshoot the CONDITION that is failing and thus it will also help you fix the complex IF statement.

I hope that this information was helpful. In case it has also resolved your latest concern, please mark it as a solution as well to give more visibility of other community members 😊