How can I create a report where I can choose to view data by month, quarter or year?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I am creating an app for overtime reporting. I need to make a chart that I can place on the homepage where along the top it will have all 12 months or 8 quarters (2 years) for the year(s) and along the side it will be the employee name. The chart will be filled with the number of overtime hours each person has logged for the given time period. 

Photo of Anna

Anna

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of Jesus

Jesus

  • 0 Points
You need a summary report to do that.

Summarize Amount of OT Hours, Display as Normal Value

Group by: Date Reported, Combine: Quarter

Group Columns

Group by: Employee Name, Combine Equal Values

Then use filters to filter the year or years you want to include in your report.

Hope it helps!
Photo of Anna

Anna

  • 0 Points
Very helpful, thank you! One more question - the way I set it up is by pay period, so for example a person will submit a certain number of overtime hours per pay period. The problem is that if the pay period starts in June and ends in july, I want to be able to pro-rate it. For example if they request 10 overtime hours for a pay period that has 1 day in June and 9 days in July, I want it to show up in the table summaries as 1 hour in June and 9 hours in July. Does that make sense?
Photo of Jesus

Jesus

  • 0 Points
Yeah it makes sense and there's a solution for that.

When you group by certain parameters, in this case date reported and quarter, hover the mouse to the right of the drop down menu where you select the time period (in this case Quarter).

You should be able to see some hidden options: A plus and a minus sign.

Click the plus sign and select:

Group by: Date Reported, Combine: Month

Quickbase will now sort the report in quarters and in the same report you should see the hours per monthly basis as well.

Let me know if that works.
Photo of Anna

Anna

  • 0 Points
That is not exactly what I was looking for- it now shows both month and quarter, but doesn't solve the issue of if the pay period is partially in june and partially in july, all the hours are still allocated to July rather than prorated so some are allocated to june. Also, I would prefer to only see quarter on the chart. Is that possible? Thank you!
Photo of Jesus

Jesus

  • 0 Points
Ah, in order to do that you'd have to play around with Formula-Date fields where you can control exactly the dates you need to. Then organize them in the reports. I can be of better assistance if you invite me to your app.

In order to see the Quarter only just delete the "month" group by hovering next to it and clicking the minus button.
Photo of Jesus

Jesus

  • 0 Points
You will have to use the Case() function for what you're trying to achieve.

Case([YourMonthHere], 1, TotalHoursWorked, 2, TotalHoursWorked... ,12, TotalHoursWorked)

This is just an example of how the formula would look like and how it'd be used. Basically you'd be recording the time as you'd normally do but QB is going to assign each day to a month. 1 being January, 12 being December and so on.

QB will collect all hours in month 1 and display them under January. Any partial hours will be recognized and picked up by the next group which is 2 (or February) and it will continue to do so even when the year changes.