active projects trend graph

  • 0
  • 1
  • Question
  • Updated 6 months ago
  • In Progress
I'm trying to create a stacked graph that will show all active projects per months (eg. last 6 months), where I would include following:
* all active projects 6 months ago, 
* then all active projects 5 months ago (exclude closed ones and include new ones) and so on, but those projects should be in the aging group (i've already created a field for that).

I have project start date, and project close date fields, and I am trying to find the best solution for this.

Thank you in advance.
Photo of Nenad Ilic

Nenad Ilic

  • 734 Points 500 badge 2x thumb

Posted 12 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,546 Points 50k badge 2x thumb
My suggestion is to create a formula field to calculate the “Aging bucket”. Then let that be the “Series” in your chart. Nite that any particular project can only appear in one “Aging bucket”.
Photo of Nenad Ilic

Nenad Ilic

  • 734 Points 500 badge 2x thumb
Hi, 

aging is not the issue, I already have that one solved for the series, the problem is the X axis. That axes should present months, and the bars should show number of active projects in that specific month, 

Like in the chart bellow.
I've resolved the part regarding project aging, the only problem I'm having is to set active projects in each month.

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,546 Points 50k badge 2x thumb
The challenge will be that in Quick Base a Project can only appear in one spot on the chart. It can't for example span 3 months. You would need to run some kind of script or process to create individual records for each Project-Month Record.
Photo of Nenad Ilic

Nenad Ilic

  • 734 Points 500 badge 2x thumb
or some summary field where I would summarize project that started before some date, and have not ended by the same date.... but then I would have 6 separate summary fields.... maybe I can use that as a starter
Photo of Kevin O'Boyle

Kevin O'Boyle

  • 602 Points 500 badge 2x thumb
You need to create a second table that contains a monthly snapshot of your active projects. Use the table-to-table import function of Quickbase. Include fields to filter on (I.e, status) and the field that will be the series in the stacked bar chart. The date you do the import (your snapshot date) can be the x-axis in your stacked bar chart.
Photo of Nenad Ilic

Nenad Ilic

  • 734 Points 500 badge 2x thumb
Hi Kevin, can you specify what you mean exactly when you say "snapshot of active projects"?

I understand the part regarding creating a table, and relationship, and maybe even lookup fields,... but could you clarify a bit more about what you mean?
Photo of Kevin O'Boyle

Kevin O'Boyle

  • 602 Points 500 badge 2x thumb
By snapshot, I mean an exact copy of all the records in your table at a point in time. To make this 'active projects by month' chart, you need to compare active projects in one month vs prior months. Go to fields, click import into the upper right, then select the third option 'import from one table to another table'. You'll need to run this import once a month to make a copy of the table data
Photo of Simon Georg Decker

Simon Georg Decker

  • 70 Points
Hi Kevin, side question: Is there a way to automate the monthly creation of a snapshot?
Photo of Kevin O'Boyle

Kevin O'Boyle

  • 602 Points 500 badge 2x thumb
Hi simon,

Two options:
1) you can create a formula url button that calls the RunImport api. You need to have someone press the button (you could set up a subscription that would email you the button monthly - and then you press it)

URL format:
https://target_domain/db/target_dbid?...
&apptoken=app_token&ticket=auth_ticket

Option 2: use a tool like zapier or Workato. I personally like zapier because it’s simple and cheap. This would let you fully automate the snapshot by scheduling it to run at a desired time. You need to still call the runimport api from zapier or Workato (If you don’t want to use zapier or Workato, you could use windows task scheduler to accomplish the above with a .bat file with the syntax start iexplore then the url)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,546 Points 50k badge 2x thumb
Simon

Automations were just released into General Avsilability last night weekend. It was supposed to be released with automatic scheduling ability. But I guess that aspect will come soon.

I suggest that set up an Automation to run a table import and expect that scheduled Automations will come soon.