Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Cumulative Unfinished Projects

Hi,

I need both a bar graph and the underlying details (in a table) to show projects when they started until they finish, carrying over unfinished projects into the next quarter.

 

Basically the data looks at the original status, the new status and reflects the status until something changes.  I can figure out a measure but cannot seem to generate  a table that will give me projects until they reach done.  I am assuming that we are still in Q3 2023.  Can anyone please help me out? Thanks 

 

 

ProjectFiscal Year Quarter StartFiscal Year Quarter CompletedStatusNew Status
A2022 Q22022 Q4In ProgressDone
B2022 Q3 Not Started 
     
     
     
     
Results Table    
ProjectFiscal Year QuarterStatus  
A2022 Q2Progress  
A2022 Q3Progress  
A2022 Q4Done  
B2022 Q3Not Started  
B2022 Q4Not Started  
B2023 Q1Not Started  
B2023 Q2Not Started  
     
Need Help

15 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Your source data is not in a usable format.  Should be more like

     

    Project Fiscal Year Quarter Status
    A 2022Q2 In Progress
    A 2022Q4 Done
    B 2022Q3 Not Started

     

    Once you have that you can use a disconnected table with your quarters and for each quarter calculate the latest recorded status for each project.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks, could you expand a bit more once I have those two tables? I just struggle with how to get the results in a table versus a simple bar graph. I really need the results table to show each quarter for each unfinished project. Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    This is amazing. Thanks you!  The final add is to get a count and sum of count if the value is populated. I cannot get the has one filter to work with these relationships.

    Thanks

    Brendan

     

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago
        Fiscal Year QuarterProjectStatusCount
        2022Q1A 0
        2022Q1B 0
        2022Q2AIn Progress1
        2022Q2B 0
        2022Q3AIn Progress1
        2022Q3BNot Started1
        2022Q4ADone1
        2022Q4BNot Started1
        2023Q1A 0
        2023Q1BNot Started1
        2023Q2A 0
        2023Q2BNot Started1
        2023Q3A 0
        2023Q3BNot Started1
        2023Q4A 0
        2023Q4BNot Started1
        Total  9
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Okay, I originally tried to do two queries (1 a max per quarter per project and 1 per data and then tried to filter out only the matches. I am still am left with the original issue of how to carry over the unfinished projects. 

     

    The Filter would just give you the active projects in a quarter so I don't see how that really complicates things but if I didn't give the option to filter, how would you approach it?