Best practices for making a rolling report by month and year

  • 0
  • 1
  • Question
  • Updated 3 weeks ago
  • Answered
I have a need to show a summary report with Months on the X Axis. This report shows labor hours by month. As jobs can last more than a year, we have a need to show these summaries also by year/month. Instead all of the labor hours for January 2018 and 2019 are being summarized in one column. 

Are their any best practices for concatenating or keeping the report from doing this?
Photo of Rob Salaj

Rob Salaj

  • 160 Points 100 badge 2x thumb

Posted 3 weeks ago

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

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
Are you saying that you only want the report to have 12 months wide and you have it also doing row groupings by year?
Photo of Rob Salaj

Rob Salaj

  • 160 Points 100 badge 2x thumb
My Rows are by cost code and phase which i need by job. My columns are by Month today. I need to add year to the columns too, as i need the report to continue to expand. Since you can only have one grouping column, i am stuck. i am thinking a concatenation field may help but the text format causes other sorting issues. for example it shows 1-2018, then 1-2019, 10-2018, 11-2018, as opposed to a monthly order. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
So just make a field to use for your column grouping that calculates to YYYY-MM

Year & "-" & PadLeft([Month],2, "0")


Photo of Rob Salaj

Rob Salaj

  • 160 Points 100 badge 2x thumb
Perfect! Thanks
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
That PadRight was a new feature on Sunday. It could be be done before, but now it's easier.