Create a chart based on a date range with value per day

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered
Here is the scenario:

We have a media product with a date range that will be purchased to run between 11/1 and 12/31 for example. The total cost is $5000.

What I need to be able to chart is the value per day for each month/quarter/year for all products.

I currently have my charts running on the end date, but that means the whole $5000 would get booked in December, which is incorrect. I need to find a way to chart the value per day based on the date ranges and then put it in a chart that shows columns of months or quarters per year.

Thank you for all your help!
Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb

Posted 5 years ago

  • 0
  • 1
Photo of Eric

Eric

  • 40 Points
What you would need to do is create a child table, let's call it Dates for example, with a Date field and a numeric currency field on it. You'll need to create one child record for the start of the period (e.g. 11/1 in the example you gave) and what the dollar amount for that single day would entail (e.g. $5000/61 days is $81.97 per day). Once that's in place, setup a recurring record button on the Dates table as outlined here: http://quickbase.intuit.com/developer/knowledge-base/how-do-i-easily-create-recurring-record-or-recu... and press the link on this new Date record. In the popup that appears select 60 more days and QuickBase will create a 11/2, 11/3, 11/4, etc. record each with $81.97 as the amount. You could then create your report for the month/quarter/year you were looking for.