Knowledge Drop

How to sort by rolling month

  • 7 July 2021
  • 1 reply
  • 95 views

Userlevel 5

Last tested: Oct 4, 2019
 

To sort their data by current month for ex: October 2019 thru November 2018, you can throw this in the lookml (dialect specific may be required):
 

dimension: this_month_num {

   hidden: yes

  sql: MONTH(CURDATE());;

 }    

 

dimension: sort_relative_month {

   type: number

   sql: CASE

         WHEN (${this_month_num} - ${created_date.month_num}) = 0

         THEN 12

         WHEN (${this_month_num} - ${created_date.month_num}) < 0

         THEN 12 - -1*(${this_month_num} - ${created_date.month_num})

         ELSE ${this_month_num} - ${created_date.month_num}

         END;;

 }

 

 

This content is subject to limited support.                

 

 


1 reply

Salemlocal

 

Reply