How to get max date for use in CASE WHEN statement in LookML

Ben_Zitney
Participant III
  • I have a dimension ${date} which references the DATE column in my table
  • I have another measure ${revenue} that references the REVENUE column in my table
  • I need to create a separate measure that returns the REVENUE value only if DATE = MAX(DATE)
  • sql parameter for the lookML would essentially be: CASE WHEN ${date} = MAX(${date}) THEN ${revenue} END
  • However, this isn’t currently possible because you can only use MAX() in a measure. Which doesn’t work with this scenario because I can’t compare the ${date} value to a measure value in the sql (I get an error in this case)
  • The only workaround I have found so far is to create a separate view with a derived table that just returns MAX(DATE) from my table, and then reference that dimension from the separate view in the CASE WHEN statement.
  • Does anyone have a better way I can accomplish what I’m trying to do?
1 0 1,222
0 REPLIES 0
Top Labels in this Space
Top Solution Authors