Forum Discussion

DeanOusterhout's avatar
DeanOusterhout
Qrew Assistant Captain
8 years ago

How to create a Bar Chart Report where the Bars are summarized based on two different date fields?

We have a table, Projects, with a SellingPrice and we track events that occur to the project, the date a customer said yes(SaleDate) and the date we received payment(PaymentDate).

We would like a report that shows how much in sales we had per month as well as the amount we received per month where we have two bars for each month.

I can easily do each of these separately, but would like to combine them onto one report, versus having to look at two different ones.

Any ideas?  QB support says their OOB product does not do this.  It seems to me I read a conversation about doing this with javascript, but I seem to have misplaced the links.

Thanks in advance for the help.

Dean

3 Replies

Sort By
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    Dean,

    You can do this with native QuickBase,  you just need to make a Summary Table.  

    Make a  new table called "Months" and you will summarized both values back to the months table, then make your report there.

    Steps:
    1- Make Months Table
    2- Make a Text Fields Called [Month ID]
    3- Set this text field as the "Key Field"
    4- Make a date field, and plug in the first date of the month.  (this will be used later for your reporting later)

    5-10
     In excel or some other data set generator you will need to make a list of all the months, past and future, for as far as you want.  Duplicate the values twice, so you will have 2 identical columns
    eg. 
    1-1-16    1-1-16
    2-1-16    2-1-16
    3-1-16    3-1-16
    etc           etc

    You will import these new records into the Months table.  So you would make anywhere from 36 to 100 records (36 to 100 months).  
    Import into the Month ID and the date field.

    11- Create a relationship where each 'month' has many 'projects',  Set it to be related month based on your Month ID which is the key field.

    12- On your projects table, change the [Related Month] field to be a formula text field.

    13- Insert a formula that will automatically connect the 'project' to the 'month' based on the date of the [Sale Date]:

    ToText( FirstDayOfTheMonth([Sale Date]))

    14- Create a summary field back to the months table that summarizes the appropriate value for the "Sale"

    15- Repeat steps 11-14 with a new relationship for the [Payment Date]

    16- Create your Bar chart with those 2 summarized values, grouped by month (the date field on the months table).

    17- Like this post :)
  • DeanOusterhout's avatar
    DeanOusterhout
    Qrew Assistant Captain
    8 years ago
    This looks like a very easy way to do this.  Thanks.  The only part I don't like about QuickBase is the need to create all these extra relationships and fields in order to do what usually can be accomplished with SQL.

    I will give this a try.
  • AnnSwelgin's avatar
    AnnSwelgin
    Qrew Trainee
    7 years ago
    I have pretty much the same question. Permit draft date and permit acquired date. I want to show how many records per month in each "category" drafted or acquired. Is there an easy way to make a calculated column in the bar chart to do this? I was thinking Case so that it would put the case values on the Y axis.. but I am not that advanced yet :)