Question

Nested if condition in filters

  • 13 November 2018
  • 5 replies
  • 1439 views

Hi ,

Can looker support nested If condition in custom filters. for example , if I have several categories of data to be displayed and only three of them are considered and I need to display these three and rest can fall into ‘Others’ bucket . I do not have permissions to edit LookML/Custom SQL so have to do this in my “look”.


something like :


if(cat = “A”,“A”,(if (cat = “B”,“B”,if(cat = “C”,“C”,“Other”) ))


5 replies

Userlevel 1

Hi Vishal,


So in a custom filter we can only filter out data. So we could filter for cat = A, B, C but we won’t be able to group cat = D, E, F, etc. into “other”.


We can, however, use a custom dimension to achieve this. We would do this using the nested if() statement you have: if(cat = “A”,“A”,(if (cat = “B”,“B”,if(cat = “C”,“C”,“Other”) ))


This would yield the results:


cat
-----
A
B
C
Other



Cheers,

Adina

Userlevel 1

Also, just to answer this question, in case anyone is looking for this specifically: Can looker support nested if condition in custom filters?. We do support nested if() statements in custom filters. An if() statement in a custom filter must evaluate to a yesno. So an if() statement would be of the form: if(yes_no_condition, yes, no) and here is an example of a nested if() statement using this form: if(${view.dimension}=A, if(${view.dimension}=B, yes, no), no).


As a note: this yields the same results as ${view.dimension}=A OR ${view.dimension}=B so for simplicity, you might want to use the latter.

 

So I had tried this code in a custom filter but it errors out with SQL error. I am curious if anybody can see what I am doing wrong? The issue that I am trying to solve is that I have a custom filter that has: 

extract_days(${transactions.transaction_date}) < extract_days(now())

This code work every day other than the first to compare the revenue earned up to yesterdays date so when the report runs early morning it shows the an equal comparison of days for an equal comparison of revenue at that point in time. 

The issue is that on the first this code needs to include the first so I get to see what happened on the first of the month and the view does not show all blanks. This code works on the first

extract_days(${transactions.transaction_date}) <= extract_days(now())

So i tried the nested if statements to get it so that on the first run the <= on all other days run the < code. Below was the code i tried. I actually tried several ieterations of this but all fail with a Microsoft SQL error. 

Example logic:

if(yes_no_condition, yes, no)
if(extract_days(now())=1,extract_days(${transactions.transaction_date}) <= extract_days(now()),extract_days(${transactions.transaction_date}) < extract_days(now()))

However the above code just fails with SQL error. So can anybody help me come up with code so that on the first of the month it uses <= every other day it uses just <?

 

Thank You,

Userlevel 6
Badge

Don’t change the sign.. keep <= but for  days bigger than 1 just add offset to the right side of the expression with add_days

Something like that:

extract_days(${transactions.transaction_date}) <= extract_days(add_days(if(extract_days(now()) = 1, 0, -1), now()))

 

@Dawid_Nawrot thank you so much!!! worked like a champ. I am so grateful. I love learning and it took me a minute or two to eventually figure out what you did her. Love it!!

Reply