Formula Help - different rows
I'm looking to create a % increase/decrease formula to total a weekly number of roofing starts and compare to the previous week with an output of increase or decrease as a percentage.
...
Forum Discussion
Hi Anthony:
If you are collecting statistics like the number of jobs on any given day and you want to measure the incremental % of change that is happening each day, you can do that with a formula date field and a table relationship to itself.
First we set the Key Field of the Table to the Date field.
Then we create a formula date field to calculate what the Previous Date is. ToDate(WorkdayAdd(ToWorkDate([Date]),-1)) or see the formula in the video.
Then we can create a relationship where this table is related to itself (Jobs have many Jobs). Make sure that during the creation of the relationship you just don't accept the default reference field but that you select the formula date field you created. You can then add a lookup field to pull down that previous day's job numbers. (See. You are using this relationship to itself to bring down the previous day's numbers.)
Now you have a record of today's jobs and the previous days jobs too. With both of these numbers you can create a formula numeric field that calculates the % of change from one day to the next. i.e., The # Jobs-Previous Date less the # Jobs today and then divide this by the # Jobs today.
We cover things like this in our daily "Office Hours" webinars. They are each M-F at 1:00pm Eastern time. You can register at
http://quickbase.com/webinars/.
------------------------------
Kirk Trachy , Senior Solutions Consultant
QuickBase | 603-674-5454 M | ktrachy@quickbase.com
------------------------------
If you are collecting statistics like the number of jobs on any given day and you want to measure the incremental % of change that is happening each day, you can do that with a formula date field and a table relationship to itself.
First we set the Key Field of the Table to the Date field.
Then we create a formula date field to calculate what the Previous Date is. ToDate(WorkdayAdd(ToWorkDate([Date]),-1)) or see the formula in the video.
Then we can create a relationship where this table is related to itself (Jobs have many Jobs). Make sure that during the creation of the relationship you just don't accept the default reference field but that you select the formula date field you created. You can then add a lookup field to pull down that previous day's job numbers. (See. You are using this relationship to itself to bring down the previous day's numbers.)
Now you have a record of today's jobs and the previous days jobs too. With both of these numbers you can create a formula numeric field that calculates the % of change from one day to the next. i.e., The # Jobs-Previous Date less the # Jobs today and then divide this by the # Jobs today.
We cover things like this in our daily "Office Hours" webinars. They are each M-F at 1:00pm Eastern time. You can register at
http://quickbase.com/webinars/.
------------------------------
Kirk Trachy , Senior Solutions Consultant
QuickBase | 603-674-5454 M | ktrachy@quickbase.com
------------------------------
