Discussions

 View Only
Expand all | Collapse all

active projects trend graph

  • 1.  active projects trend graph

    Posted 11-27-2017 15:09
    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.


  • 2.  RE: active projects trend graph

    Posted 11-27-2017 15:13
    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�.


  • 3.  RE: active projects trend graph

    Posted 11-27-2017 15:18
    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.



  • 4.  RE: active projects trend graph

    Posted 11-27-2017 15:22
    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.


  • 5.  RE: active projects trend graph

    Posted 11-27-2017 15:32
    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


  • 6.  RE: active projects trend graph

    Posted 11-27-2017 15:32
    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.


  • 7.  RE: active projects trend graph

    Posted 11-27-2017 15:36
    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?


  • 8.  RE: active projects trend graph

    Posted 11-27-2017 16:29
    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


  • 9.  RE: active projects trend graph

    Posted 05-23-2018 22:04
    Hi Kevin, side question: Is there a way to automate the monthly creation of a snapshot?


  • 10.  RE: active projects trend graph

    Posted 05-23-2018 22:58
    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)


  • 11.  RE: active projects trend graph

    Posted 05-24-2018 00:10
    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.