Creating an expression with multiple conditions

[midwest]

Hello, 

Apologies if this has already been asked. I tried looking through the forums for a relevant posts, but I wasn't able to find anything that could fix my problem. 

I am trying to build an expression with multiple conditions.

In the "Recent or not recent" column I want this expression to indicate whether a month is within the last three months before the current month (considering  that it is currently May, these three months would be February, March, and April 2023). I want these three months to display "Recent" as their value under this expression. I want all other months (January 2022-January 2023) to indicate "not recent" as their value. 

Here is my expression:

if(
month(service_month)-month(today())<>-1 or
month(service_month)-month(today())<>-2 or
month(service_month)-month(today())<>-3
and year(service_month)-year(Today())<>0
,'not recent', 'recent')

The column "month(service_month)-month(today())" assigns a value based on how far a month is to the current month. The three most recent months before the current month will always have the values of -1, -2, or -3, so my expression uses those three values to identify if a month is considered recent. However, months from the previous year will also have those values, so I also have the column "year(service_month)-year(today())" to assign a value if a month is within the same year as the current year. This way, the expression should check if a month has the values indicating a recent month (-1,-2,-3) and if that month is within the same year as the current year (0 for the same year, -1 for the previous year). 

My expression is not working, as every cell has the value "not recent". The rows that have -1, -2, or -3 under the expression "month(service_month)-month(today())" and 0 under the expression "year(service_month)-year(today())" should be "recent". 

When I try only using one condition in the expression:

if(
month(service_month)-month(today())<>-1
,'not recent', 'recent')

All of the months that have -1 under the expression "month(service_month)-month(today())" have "recent", and all other months have "not recent". When I add the second condition, either adding a condition about a second month value to look for (like -2 or -3) or adding the condition to look for the year compared to the current year, the expression breaks and assigns every cell "not recent", so I think the issue has to do with having multiple conditions in the expression.

(Expression with only one condition in the IF statement, expression works)

(Expression with an additional condition in the IF statement, expression breaks)

Thank you,

[Kushal_Chawda · Accepted solution]

@midwest  If you have Date field in your data, best way would be to create master calendar using that Date and create a flag in the script instead using front end expression

Calendar:
Load  Date,
     if( Date >= monthstart(AddMonths(Today(),-3)) and  
      Date <= monthend(AddMonths(Today(),-1)),'Recent', 
      if(Date >= monthstart(Today()) and Date <= MonthEnd(Today()),'Current Month', 'Not Recent')) as Flag,
     Monthname(Date) as MonthYear,
     Month(Date) as Month, 
     Year(Date) as Year
....

If you still want to do it in front end try below expression. Expression which you are trying uses same condition to check the different value, hence it evaluates only first condition . Other two condition doesn't evaluate because it is same condition so Qlik engine interprets that it is already evaluated first time. This is how conditional expression works

=if(monthstart(date#(Year& Month,'YYYYMMM')) >= monthstart(addmonths(Today(),-3)) and 
monthstart(date#(Year& Month,'YYYYMMM')) <= monthstart(addmonths(Today(),-1)),'Recent','Not Recent')

[midwest]

Hi,

I updated my expression based on your expression and it is working 😁

Thank you so much for the help!