So I've hunted around and haven't been able to find an answer to this so far. I'd like to make a comparison so we can see how we're doing compared to last year. So I'd like to have a report where I can say, "January 2019 estimates vs. January 2018" and maybe see a dollar amount or % of change. I can't seem to crack this nut myself...

I had the exact same issue & was pretty confused by the previously posted "solutions". But I came up with a MUCH easier solution for this. To make a Bar Chart, you can do the following: Create a Chart Report & select Bar Graph Immediately create a custom report field (I called it "Months"). This will be a number formula field - formula you will post is simple: Month(ToDate([Date Created])) Set X Axis to your custom field, set Y Axis to whatever you are trying to summarize (I just did a count field), and set the series to Date Created, grouped by Year Here is the final product:------------------------------Monster Monster------------------------------

This is not easy to do, so the solutions depend on the nature of what you are trying to summarize. The issue is that you cannot natively do arithmetic calculations on columns in a summary report.If, for example, you just wanted grand totals for the whole Company year over year, then you would need ot load up a table of say the next 20 years, set the Key field to be the year and then using summary fields get to totals you need up to that record. Then you can do all the math you like. If these were totals by say Customer, then you would need to have summary fields such as say YTD sales this year, and YTD sales last year (say to the most recently completed month) and then do the math on the customer record.

What I'm really trying to do is compare sales overall, year to year, by month. So I can say, "last January we did X, this January we did Y, which was a Z% increase." We track estimate value and won value, so I'd like to see how we're comparing w/ the previous year in estimating per month and what we've won per month.

Our sales cycle can be lengthy, so it's a good indicator of how we're moving to watch estimates. Most retail business compare against the same time period the year before, so I assumed I wasn't the first to ask this question, but I couldn't find anyone who really asked the same thing.

OK, so an easy solution is to use excel to load at able with the values201801201802201803 and all the way forward say for 20 years.Set that YYYYMM field to be the key fieldThen on the sales table make a field to calculate to the YYYYMM of the sales dateToText(Year([date of sale]))&right("0" & totext(month([date of sale])))Call it [YYYYMM of Sale Date]Use that in a relationship to roll that up to the YYYMM table.Then we also want last year salesMake a new text formula field for [YYYYMM of next year]ToText(Year([date of sale])+1)&right("0" & totext(month([date of sale]))) Make a new relationship to the YYYYMM Table and also make a summary of the salesYou should now have the two fields on the form for the sales of the YYYYMM and also the sales of the YYYMM form last year.

Report showing comparison for sales per month this year vs. last year

BrittanyLipinsk
Qrew Trainee
5 years ago
I had the exact same issue & was pretty confused by the previously posted "solutions". But I came up with a MUCH easier solution for this.

To make a Bar Chart, you can do the following:

Create a Chart Report & select Bar Graph

Immediately create a custom report field (I called it "Months"). This will be a number formula field - formula you will post is simple:
Month(ToDate([Date Created]))

Set X Axis to your custom field, set Y Axis to whatever you are trying to summarize (I just did a count field), and set the series to Date Created, grouped by Year

Here is the final product:

------------------------------
Monster Monster
------------------------------
- WayneOliva
  Qrew Member
  2 years ago
  Brittany Lipinski, Thank you so much. This helped me quite a bit. Followup: I was able to use NameOfMonth(Month([Invoice Date])) as a formula to display the names of the month, but this put them in alphabetical order (April, August, December, etc.) Do you know of a way to display the name of the month in calendar order?
  
  ------------------------------
  Wayne Oliva
  ------------------------------
  - MarkShnier__You
    Qrew Legend
    2 years ago
    Wayne. You would need to cheat on the names with varying amounts of leading spaces, or else prefix the month name like 01-Jan. 02-Feb
    
    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
QuickBaseCoachD
Qrew Captain
7 years ago
This is not easy to do, so the solutions depend on the nature of what you are trying to summarize. The issue is that you cannot natively do arithmetic calculations on columns in a summary report.

If, for example, you just wanted grand totals for the whole Company year over year, then you would need ot load up a table of say the next 20 years, set the Key field to be the year and then using summary fields get to totals you need up to that record. Then you can do all the math you like.

If these were totals by say Customer, then you would need to have summary fields such as say YTD sales this year, and YTD sales last year (say to the most recently completed month) and then do the math on the customer record.
- ChrisNewsome
  Qrew Captain
  7 years ago
  What I'm really trying to do is compare sales overall, year to year, by month. So I can say, "last January we did X, this January we did Y, which was a Z% increase." We track estimate value and won value, so I'd like to see how we're comparing w/ the previous year in estimating per month and what we've won per month.
- ChrisNewsome
  Qrew Captain
  7 years ago
  Our sales cycle can be lengthy, so it's a good indicator of how we're moving to watch estimates. Most retail business compare against the same time period the year before, so I assumed I wasn't the first to ask this question, but I couldn't find anyone who really asked the same thing.
- QuickBaseCoachD
  Qrew Captain
  7 years ago
  OK, so an easy solution is to use excel to load at able with the values
  
  201801
  201802
  201803
  
  and all the way forward say for 20 years.
  
  Set that YYYYMM field to be the key field
  
  Then on the sales table make a field to calculate to the YYYYMM of the sales date
  
  ToText(Year([date of sale]))
  &
  right("0" & totext(month([date of sale])))
  
  Call it [YYYYMM of Sale Date]
  
  Use that in a relationship to roll that up to the YYYMM table.
  
  Then we also want last year sales
  
  Make a new text formula field for
  [YYYYMM of next year]
  
  ToText(Year([date of sale])+1)
  &
  right("0" & totext(month([date of sale])))
  
  Make a new relationship to the YYYYMM Table and also make a summary of the sales
  
  You should now have the two fields on the form for the sales of the YYYYMM and also the sales of the YYYMM form last year.
ChrisNewsome
Qrew Captain
7 years ago
This seems like it should be a function of Qbase and be much easier than this. I can't be the only person who wants to compare old data.
_anomDiebolt_
Qrew Elite
7 years ago
This is easy to do with script. But your question is very broad without any reference to the desired report format, what is being aggregated or fields involved. Without regard to native QuickBase capabilities post a mockup of what you want the summary report (or chart) to look like and what fields are involved.
- ChrisNewsome
  Qrew Captain
  7 years ago
  We list jobs as "opportunities"
  We track an "estimate $" and a "won value" - each with a corresponding date
  The opportunity has a "status" of "proposal" or "new", "lost," etc.
  
  so what I'd like to do is total up the "won value" of all opportunities with a "won" status and an "won date" in the month of January 2018, and compare that to the opportunities in January 2019. (with the same criteria of won). The format of the report isn't critical, as long as it's easy to follow. I drew up how I'm visualizing it in my head, but I'm open to suggestion on this.
  
  Thanks!
- QuickBaseCoachD
  Qrew Captain
  7 years ago
  Where are you at so far in getting the summary totals up to those YYYYMM records? Did you create the YYYYMM table? Did you make the Relationship? Did you make the summary fields.
  
  Contact me directly if you want assistance getting this set up
  
  QuickBaseCoach.com
- ChrisNewsome
  Qrew Captain
  7 years ago
  Honestly I have not. I really don't know how to even begin on this. I also haven't had a ton of time to spend on it today.