Question

Table Calculation with conditional for dates

  • 5 January 2018
  • 1 reply
  • 1771 views

First of probably many questions here!



I am trying to create a table calculation that essentially says “Sum Revenue if Date is yesterday”.



I am trying for something along the lines of the below (though I know it does not make sense in Looker):



sum(


if(


${Date} = date(now())-1,


${revenue_sum},


null


)


)



I know I could filter for the date in explore and see what the revenue is, but the goal is to create a table calculation to measure what the day over day difference in revenue is.



Thanks!


1 reply

Userlevel 3

Hi @ahilans21,



You were definitely on the right track here. This would be a good time to use the diff_days() Looker function to get the difference between the current date and the ${Date} field for your if condition.



sum(

if(

diff_days(${Date}, now()) = 1,

${revenue_sum},

0

)

)



This will will check if the number of days between the ${Date} field and the current time in the query timezone, and sum all values of ${revenue_sum} where ${Date} is yesterday. Hope this helps!



Best,


Ryan

Reply