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.
QuickBaseCoachD
7 years agoQrew Captain
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.
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.