Knowledge Drop

Create Day of Quarter in Table Calc

  • 7 July 2021
  • 3 replies
  • 61 views

Userlevel 2

Last tested: Jun 15, 2021
 

This requires 3 easy steps of Table Calc magic.
Here is a picture to get an idea.

superawesometablecalc.png

The steps are below

  1. Create an extract months table calc
    extract_months(${table.date})
  2. Create an extract days table calc
    extract_days(${table.date})
  3. Create Days of Quarter, referencing the month and adding days to the days based on quarter.
    if(${month} = 2, ${day_of_month} + 31,

    if(${month} = 3, ${day_of_month} + 31 + 28,

    if(${month} = 5, ${day_of_month} + 31,

    if(${month} = 6, ${day_of_month} + 30 + 31,

    if(${month} = 8, ${day_of_month} + 31,

    if(${month} = 9, ${day_of_month} + 31 + 30,

    if(${month} = 11, ${day_of_month} + 31,

    if(${month} = 12, ${day_of_month} + 31 + 30, ${day_of_month}))))))))

Yayayay.png

The above is a potential use case for this! Good luck!

 

This content is subject to limited support.                

 

 


3 replies

Userlevel 2

thanks @Pieter_DeVries just what I needed! Not surprised you came up with something like this :joy:

Userlevel 2

Nice! I am bummed the pngs didn't survive though.

Userlevel 2

Yeah I was wondering what the images were showing.

 

For reference I used the above with this article:

https://community.looker.com/dashboards-looks-7/as-close-as-you-can-get-to-a-bullet-graph-with-looker-s-out-of-the-box-charts-actual-vs-target-16943

 

That article was great in showing MTD actuals vs target (and shows if you're ahead or behind the "expected pace" you should be at on the current date) but I used your post to modify it so that it could track QTD.

 

Thanks!

Reply