How do I use a date range in an IF statement in a table calculation? I’m trying to use a nested if statement display a variable value depending on when an action took place, essentially trying to say: IF(date before 2021-11-1,VALUE,IF(date between 2021-11-01 and 2021-12-31, VALUE, DEFAULT VALUE))
Hi Semerson,
You can try using AND/OR operators in Looker to achieve the desired output.
Please find the following example and the steps to get a date range :
1.- Include the date fields that you want to include in the date range. In my example, I would like to check if my subscriptor was created and disabled in the same year, so I will include start_date field end_date field in my explore.
2.- Create the table calculation “Filter_Range” , in this example, this code was used:
if(NOT is_null(${users_history.user_history_end_date}) AND extract_years(${users_history.user_history_start_date})= extract_years(${users_history.user_history_end_date})
AND ${users_history.user_history_start_date} >= date(2021,01,01) AND ${users_history.user_history_start_date} <= date(2021,12,31)
,"Turnover in the same year","no")
Screenshot:
3.- Create a table calculation “helper” that will allow us to filter the data based on the output value.
Example code:
if(${range_2021} ="no",no,yes)
Screenshot:
4.- Finally, you can see the two new tables calculations with the desired output:
(Note: You can use the option “hide no’s values” to hide values that doesn’t match with your table calc output)
I hope this will help
Best regards,
Leo