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

  • 0
  • 1
  • Question
  • Updated 3 weeks ago
  • In Progress
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...
Photo of moleman108

moleman108

  • 582 Points 500 badge 2x thumb

Posted 3 weeks ago

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

QuickBaseCoach App Dev./Training, Champion

  • 57,418 Points 50k badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 57,418 Points 50k badge 2x thumb
Well start into it and post back if you get stuck. :)

The point is that in order to do math between those numbers for total sales they need to hook onto something. In other words they need to be summary fields on a particular record. Do we create those YYYYMM records to capture the totals.
Photo of moleman108

moleman108

  • 582 Points 500 badge 2x thumb
BTW the only data we have is for this current year.
Photo of moleman108

moleman108

  • 582 Points 500 badge 2x thumb
2018 to be clear...
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 57,418 Points 50k badge 2x thumb
Ok so start with 201801 for the next 20 years.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 57,418 Points 50k badge 2x thumb
It is a common request but there is no easy answer.
Photo of moleman108

moleman108

  • 582 Points 500 badge 2x thumb
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.

(Edited)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 28,924 Points 20k badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 57,418 Points 50k badge 2x thumb
Well either you give up on that report, or persevere through this forum, or Contact me directly if you want assistance getting this set up
Photo of moleman108

moleman108

  • 582 Points 500 badge 2x thumb
I appreciate that. I just don't understand how to use excel to load a table. It's something I've never even heard of before. I thought this wouldn't be this complex. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 57,418 Points 50k badge 2x thumb
If you like you can hand enter the 12 records for 2018 for your testing and worry about 2019 later.

Just enter 12 records in the format
201801
201802
201803

An alternative that would be easier is to set the key field of the YYYYMM table to be a date field and then enter the date if the 1st if each month for the 12 months of 2018.

Then on the detail side of the relationship have a field called first day of the month and make the formula

Firstdayofmonth([my date field])

That will be easier for you to set up.
Photo of moleman108

moleman108

  • 582 Points 500 badge 2x thumb
Sorry to seem like an idiot, but I still don't get it. Am i making a table in QBase? Or am I making an excel spreadsheet? The data is there in QBase. Do I have to copy it out of QBase into excel just to get it back into Qbase? 

I appreciate the time you've given me on this. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 57,418 Points 50k badge 2x thumb
Ok, never mind anything to do with excel.

Make a new table called monthly stats and set the key field to be a date field.

Then follow my previous post just above this one and go from there.