Discussions

Expand all | Collapse all

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

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

    Posted 12-31-2018 16:59
    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...


  • 2.  RE: Report showing comparison for sales per month this year vs. last year

    Posted 12-31-2018 19:58
    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.


  • 3.  RE: Report showing comparison for sales per month this year vs. last year

    Posted 12-31-2018 20:10
    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.


  • 4.  RE: Report showing comparison for sales per month this year vs. last year

    Posted 12-31-2018 20:12
    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.


  • 5.  RE: Report showing comparison for sales per month this year vs. last year

    Posted 12-31-2018 20:26
    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.


  • 6.  RE: Report showing comparison for sales per month this year vs. last year

    Posted 12-31-2018 20:39
    so I would have to use Excel anytime I wanted to see these numbers?


  • 7.  RE: Report showing comparison for sales per month this year vs. last year

    Posted 12-31-2018 20:50
    No, you just need to load up a table with all the YYYYMMs for the next 20 years.  But just once.


  • 8.  RE: Report showing comparison for sales per month this year vs. last year

    Posted 12-31-2018 21:49
    wow I'm not sure I'm following that. Every time I think I'm doing OK with QB, something like this takes me back down a peg!


  • 9.  RE: Report showing comparison for sales per month this year vs. last year

    Posted 12-31-2018 21:53
    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.


  • 10.  RE: Report showing comparison for sales per month this year vs. last year

    Posted 12-31-2018 21:54
    BTW the only data we have is for this current year.


  • 11.  RE: Report showing comparison for sales per month this year vs. last year

    Posted 12-31-2018 21:54
    2018 to be clear...


  • 12.  RE: Report showing comparison for sales per month this year vs. last year

    Posted 12-31-2018 21:56
    It is a common request but there is no easy answer.


  • 13.  RE: Report showing comparison for sales per month this year vs. last year

    Posted 12-31-2018 21:56
    Ok so start with 201801 for the next 20 years.


  • 14.  RE: Report showing comparison for sales per month this year vs. last year

    Posted 12-31-2018 21:55
    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.



  • 15.  RE: Report showing comparison for sales per month this year vs. last year

    Posted 01-02-2019 13:11
    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.


  • 16.  RE: Report showing comparison for sales per month this year vs. last year

    Posted 01-02-2019 19:43
    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!


  • 17.  RE: Report showing comparison for sales per month this year vs. last year

    Posted 01-02-2019 20:27
    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


  • 18.  RE: Report showing comparison for sales per month this year vs. last year

    Posted 01-02-2019 21:44
    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.


  • 19.  RE: Report showing comparison for sales per month this year vs. last year

    Posted 01-02-2019 22:27
    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


  • 20.  RE: Report showing comparison for sales per month this year vs. last year

    Posted 01-03-2019 12:53
    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. 


  • 21.  RE: Report showing comparison for sales per month this year vs. last year

    Posted 01-03-2019 12:58
    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.


  • 22.  RE: Report showing comparison for sales per month this year vs. last year

    Posted 01-03-2019 13:03
    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. 


  • 23.  RE: Report showing comparison for sales per month this year vs. last year

    Posted 01-03-2019 13:15
    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.