I am trying to calculate a "total days taken" to complete a project. However, each project can have the same project code for each day.
Project A (project code associated: 123abc)
project code 123abc: [comment] "starting to clean", [startdate]: 01/01/2022, [enddate]: 01/01/2022, + other variables
project code 123abc: [comment] "finished cleaning, painting", [startdate]: 01/02/2022, [enddate]: 01/03/2022+ other variables
project code 123abc: [comment] "paint and clean finished", [startdate]: 01/04/2022, [enddate]: 01/05/2022+ other variables
Goal is to see Total Days Completed" for Project A: **4** days
Can Spotfire calculate this somehow, considering multiple project codes that are under the same code? Or is there a while loop that can be made to evaluate "while (project code is the same), [Day Count] ++" ?
Thank you for any advice.
There are different ways in which you could calculate it. You don't specify you want it in a column or a separate view for instance.
If you want a calculated column, you could have a [Project duration] as:
DateDiff('day',Min([startdate]) over ([project_code]),Max([enddate]) over ([project_code]))
If you want a separate summary view, you could use a KPI visualization, and set up a KPI with Values:
Value: DateDiff('day',Min([startdate]),Max([enddate]))
Tile by: project_code
You don't say what you want to do with the additional variables, but you could use them to slice the calculation, so instead of
over ([project_code])
you could do for the calculated column
over (Intersect([project_code],[other_variable1],[other_variable2]))
and tile the KPI over the additional variables.
There are resources on YouTube (e.g. the Dr Spotfire series) to help with the Over functionality in Spotfire.