Forum Discussion
Creating dynamic groupings based on dates
Hi Clint
I would suggest you to create calcuated columns.
First go to Edit queries->Add column->add year, quarter, day
Then close&apply, create a calcuated column, then add this column as the Axis value instead.
Column = VAR NEW_QUARTER = SWITCH ( [Quarter], 1, "2.MQ", 2, "3.JQ", 3, "4.SQ", 4, "1.DQ" ) VAR YEAR = RIGHT ( [Year], 2 ) RETURN CONCATENATE ( NEW_QUARTER, YEAR )Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Maggie,
Sure. In any given quarter we'll have a range of projects that have Feature Complete dates in that quarter.
The FC Baseline field is actually a column called Featuretargetcomplete in the Projects table from Project Server. I created a group on that field and grouped the dates in groups called 1.DQ18, 2.MQ19, 3.JQ19, This allowed me to create a chart that looks like this:
The problem is, when I grouped the dates into quarters, I could only group dates for the current projects. So, if/when new projects come in (or dates change), the groups will not adjust dynamically to reflect these changes. How do I create groups that automatically group dates into the right quarters?
Hi Clint
I would suggest you to create calcuated columns.
First go to Edit queries->Add column->add year, quarter, day
Then close&apply, create a calcuated column, then add this column as the Axis value instead.
Column = VAR NEW_QUARTER = SWITCH ( [Quarter], 1, "2.MQ", 2, "3.JQ", 3, "4.SQ", 4, "1.DQ" ) VAR YEAR = RIGHT ( [Year], 2 ) RETURN CONCATENATE ( NEW_QUARTER, YEAR )Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thanks Maggie!! I will give this a shot.