Date Range in IF Statement in Table Calculation

[semerson]

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

[leobardor]

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:

Please see the part “AND” operator, this operator allow us to create the range.
“date” function in looker allow us to create any valid date.

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