I have a column with dates and time in one column and in the adjacent column there are corresponding production numbers. What I would like to do is sum the corresponding production numbers BY DAY in the first column. i.e one total at the end of each day in a third column. This is the parent table.
-
80 Points
Posted 8 months ago
Quick Base Junkie (find me on YouTube)
-
1,384 Points
If you group your records by Day, you'll get a record count on the group header.
You could then also "show summary table based on the grouped columns" to get a summary at the top of your report that may be a little easier to read.
Here is an example of the report settings:
It isn't exactly what you described, but may provide you with the same info without too much effort.
You could then also "show summary table based on the grouped columns" to get a summary at the top of your report that may be a little easier to read.
Here is an example of the report settings:
It isn't exactly what you described, but may provide you with the same info without too much effort.
Ⲇanom the ultimate (Dan Diebolt), Champion
-
30,064 Points
You appear to be asking for a cumulative sum. This can be done using the OEH Technique using this formula:
Cumulative Sum
https://haversineconsulting.quickbase.com/db/bnxqbmgw4?a=q&qid=1
Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=674
"<img src onerror='window.QBU_Sum=window.QBU_Sum+" & [Quantity] & "||(window.QBU_Sum=" & [Quantity] & ");this.outerHTML=QBU_Sum'>"
Cumulative Sum
https://haversineconsulting.quickbase.com/db/bnxqbmgw4?a=q&qid=1
Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=674
-
80 Points
This is close to what I'm after but i need to go one step further. Using the attached picture, I'd like to sum the numbers in the left column according to the days in the right column.
(Edited)
QuickBaseCoach App Dev./Training, Champion
-
65,472 Points
If you do a regular Summary report you can choose to have a column show as a running total down the page.
-
272 Points
Hi! Follow up question: is there a formula for a field to do this so that it shows up in a grid edit report?
QuickBaseCoach App Dev./Training, Champion
-
65,472 Points
Yes, there is a way, actually. I will post back in a few hours.
QuickBaseCoach App Dev./Training, Champion
-
65,472 Points
.So here is an idea.
Using Excel, Load up a table with all the consecutive days between now and say 10 years from now. Set the key feild to be the date field.
Make a formula field on the table which calculates the previous day from that key date field.
[Date] - Days(1)
Make a relationship of the table to itself and on the right side of the relationship for the reference field use that formula field. QuickBase will let you make a relationship of a table to itself it will just doublecheck with you to make sure that’s what you really mean.
So now you can make another relationship of the state table down to your detailed records. Of course on the right-hand side of the relationship you will need to have a formula field if you don’t already have a clean date field on the detail table. It looked from your example that you had a date time field.
So now the data table can have that relationship do a summary of the Production numbers for that day. And then based on the relationship of the data table to itself can look up the previous days total production into the current days total production to have a running total. So now that running total can be locked up back down into your production dates table.
Of course somehow you will have to decide when the running total starts at zero.
Using Excel, Load up a table with all the consecutive days between now and say 10 years from now. Set the key feild to be the date field.
Make a formula field on the table which calculates the previous day from that key date field.
[Date] - Days(1)
Make a relationship of the table to itself and on the right side of the relationship for the reference field use that formula field. QuickBase will let you make a relationship of a table to itself it will just doublecheck with you to make sure that’s what you really mean.
So now you can make another relationship of the state table down to your detailed records. Of course on the right-hand side of the relationship you will need to have a formula field if you don’t already have a clean date field on the detail table. It looked from your example that you had a date time field.
So now the data table can have that relationship do a summary of the Production numbers for that day. And then based on the relationship of the data table to itself can look up the previous days total production into the current days total production to have a running total. So now that running total can be locked up back down into your production dates table.
Of course somehow you will have to decide when the running total starts at zero.
-
272 Points
Hello again,
I wasn't able to make the comment directly above work for what I need, however, I was able to make the original formula for the cumulative sum to work.
My next question is then how do I get those results to convert to numbers for formula use? Basically if I want to subtract the cumulative results from another summary field number, is there a way to do that?
Based on a preliminary search I'm not seeing anything on like a 'ToNumber' for rich text formula fields...
I wasn't able to make the comment directly above work for what I need, however, I was able to make the original formula for the cumulative sum to work.
My next question is then how do I get those results to convert to numbers for formula use? Basically if I want to subtract the cumulative results from another summary field number, is there a way to do that?
Based on a preliminary search I'm not seeing anything on like a 'ToNumber' for rich text formula fields...
