Forum Discussion
QuickBaseCoachD
7 years agoQrew Captain
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.
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.
- ChrisNewsome7 years agoQrew CaptainWhat 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.
- ChrisNewsome7 years agoQrew CaptainOur 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.
- QuickBaseCoachD7 years agoQrew CaptainOK, 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. - ChrisNewsome7 years agoQrew Captainso I would have to use Excel anytime I wanted to see these numbers?
- QuickBaseCoachD7 years agoQrew CaptainNo, you just need to load up a table with all the YYYYMMs for the next 20 years. But just once.
- ChrisNewsome7 years agoQrew Captainwow 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!
- QuickBaseCoachD7 years agoQrew CaptainWell 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. - ChrisNewsome7 years agoQrew CaptainBTW the only data we have is for this current year.
- ChrisNewsome7 years agoQrew Captain2018 to be clear...
- QuickBaseCoachD7 years agoQrew CaptainIt is a common request but there is no easy answer.