Forum Discussion
MCFNeil
9 years agoQrew Captain
Dean,
You can do this with native QuickBase, you just need to make a Summary Table.
Make a new table called "Months" and you will summarized both values back to the months table, then make your report there.
Steps:
1- Make Months Table
2- Make a Text Fields Called [Month ID]
3- Set this text field as the "Key Field"
4- Make a date field, and plug in the first date of the month. (this will be used later for your reporting later)
5-10
In excel or some other data set generator you will need to make a list of all the months, past and future, for as far as you want. Duplicate the values twice, so you will have 2 identical columns
eg.
1-1-16 1-1-16
2-1-16 2-1-16
3-1-16 3-1-16
etc etc
You will import these new records into the Months table. So you would make anywhere from 36 to 100 records (36 to 100 months).
Import into the Month ID and the date field.
11- Create a relationship where each 'month' has many 'projects', Set it to be related month based on your Month ID which is the key field.
12- On your projects table, change the [Related Month] field to be a formula text field.
13- Insert a formula that will automatically connect the 'project' to the 'month' based on the date of the [Sale Date]:
ToText( FirstDayOfTheMonth([Sale Date]))
14- Create a summary field back to the months table that summarizes the appropriate value for the "Sale"
15- Repeat steps 11-14 with a new relationship for the [Payment Date]
16- Create your Bar chart with those 2 summarized values, grouped by month (the date field on the months table).
17- Like this post :)
You can do this with native QuickBase, you just need to make a Summary Table.
Make a new table called "Months" and you will summarized both values back to the months table, then make your report there.
Steps:
1- Make Months Table
2- Make a Text Fields Called [Month ID]
3- Set this text field as the "Key Field"
4- Make a date field, and plug in the first date of the month. (this will be used later for your reporting later)
5-10
In excel or some other data set generator you will need to make a list of all the months, past and future, for as far as you want. Duplicate the values twice, so you will have 2 identical columns
eg.
1-1-16 1-1-16
2-1-16 2-1-16
3-1-16 3-1-16
etc etc
You will import these new records into the Months table. So you would make anywhere from 36 to 100 records (36 to 100 months).
Import into the Month ID and the date field.
11- Create a relationship where each 'month' has many 'projects', Set it to be related month based on your Month ID which is the key field.
12- On your projects table, change the [Related Month] field to be a formula text field.
13- Insert a formula that will automatically connect the 'project' to the 'month' based on the date of the [Sale Date]:
ToText( FirstDayOfTheMonth([Sale Date]))
14- Create a summary field back to the months table that summarizes the appropriate value for the "Sale"
15- Repeat steps 11-14 with a new relationship for the [Payment Date]
16- Create your Bar chart with those 2 summarized values, grouped by month (the date field on the months table).
17- Like this post :)