Calculating quarters using a table

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I have been using a calculated field to calculate quarters.  So far it has worked well.   but reality is that not in all cases quarterly calculation is correct.  There are situations which is quite common in the construction industry to push the quarter further into the new month or sometimes to meet accounting close off requirements.  So there is some subjectivity to this business. 

My question is if I were to create a table of quarters by the month end date for our business, how do I use a calculated field in my primary projects table to report a correct quarter by matching the date a project is opening?  As an example this is what I was using in Excel to get around the issue. 

Mon 28-Sep-2015

Qtr 2-2016

Mon 28-Dec-2015

Qtr 3-2016

Mon 28-Mar-2016

Qtr 4-2016

Mon 27-Jun-2016

Qtr 1-2017

Mon 26-Sep-2016

Qtr 2-2017

Mon 26-Dec-2016

Qtr 3-2017

Mon 27-Mar-2017

Qtr 4-2017

Mon 26-Jun-2017

Qtr 1-2018

Mon 25-Sep-2017

Qtr 2-2018




        
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,132 Points 3k badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
No problem,

Make a new table called Quarters

using Excel load up the table of the next 10 years of dates (ie all 365 per year) and specify their Quarter in the format say YYYY-QQ (so they will sort properly.).  I would probably have a drop down for the YYYY and a drop down for the QQ and then use a formula to make the YYYY-QQ format to ensure perfect consistency.

On your details record make a formula field equal to the date field of the record and use THAT field to make a relationship to the Quarter table and look up the Quarter.  Th reaosn to have the relationship be via that extra hop formula is so that your date picker field son the detail records still look like the regular calendar.


Yes, it's a extra table with 3,650 records but just load it up good to last 10 years, and once its all working hide it from most Roles.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,132 Points 3k badge 2x thumb
Thanks a lot Mark. Would I be able  to see the quarter automatically or the quarter will need to be selected?  I prefer an automatic reference to the right quarter.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,256 Points 50k badge 2x thumb
Each record containing the Date will look up the quarter when the record is saved.  There is nothing manual to select.