Expand my Community achievements bar.

Calculated fields for Custom Fiscal Year Quarters-adding to Requests

Avatar

Level 3

Since the system custom quarters don’t contain enough entries and doesn't apply to Gantt views, I attached project and task custom forms with calculated fields for fiscal year quarters for actual start and actual completion dates that I can use for reporting.

To do the same thing for requests, I’m going to either 1) add a custom form section with calculated fiscal year quarters in each request custom form or 2) add an additional issue custom form with calculated fiscal year to each request topic containing the main request form. I’m leaning towards option 2, although then I have to manually mass add the request fiscal year quarter calculation custom form to all requests and their converted objects that are already in the system. Option 1 is a large initial work and maintenance project since we have 70 request custom forms and there will be more, so any changes in the fiscal year calculated fields will require individual form edits. I’m trying to limit tasks and projects from having many custom forms attached since that looks confusing to the user, I think, but I don't like the maintenance required for option 1.

If anyone else has added fiscal year/quarter calculated fields to projects/tasks/requests for reporting, what process did you use? Thanks

2 Replies

Avatar

Community Advisor

CBuckwal, I developed a form called Global Reporting which I use to hold any unique fields various teams wanted for reporting purposes.  I told all teams that no matter what templates you build the one custom form that must always be attached is the Global Reporting form.  I did a mass update to add this form to every project as you noted so it would be out there if we needed to capture historical info.  On this custom form I have built sections to denote our various teams and as they ask me for new or different things for their reporting I usually add it to this form so I can have it update across any object or project its a part of, sounds like this might help you as well.  This form is associated with most objects like project, issue, program, etc. so I can use it where needed across my whole environment.  I had one group that wanted to see quarters for a specific custom field date they captured so they could report on what quarter the win/loss happened.  Below is the calculated field of that if you needed something similar, I called the field win/lost quarter and it gave at Q1 2024, Q2 2024, etc which is what they wanted for reporting.

 

IF(MONTH({DE:Win/Loss Status Updated Date})=1,CONCAT("Q1"," ",YEAR({DE:Win/Loss Status Updated Date})),IF(MONTH({DE:Win/Loss Status Updated Date})=2,CONCAT("Q1"," ",YEAR({DE:Win/Loss Status Updated Date})),IF(MONTH({DE:Win/Loss Status Updated Date})=3,CONCAT("Q1"," ",YEAR({DE:Win/Loss Status Updated Date})),IF(MONTH({DE:Win/Loss Status Updated Date})=4,CONCAT("Q2"," ",YEAR({DE:Win/Loss Status Updated Date})),IF(MONTH({DE:Win/Loss Status Updated Date})=5,CONCAT("Q2"," ",YEAR({DE:Win/Loss Status Updated Date})),IF(MONTH({DE:Win/Loss Status Updated Date})=6,CONCAT("Q2"," ",YEAR({DE:Win/Loss Status Updated Date})),IF(MONTH({DE:Win/Loss Status Updated Date})=7,CONCAT("Q3"," ",YEAR({DE:Win/Loss Status Updated Date})),IF(MONTH({DE:Win/Loss Status Updated Date})=8,CONCAT("Q3"," ",YEAR({DE:Win/Loss Status Updated Date})),IF(MONTH({DE:Win/Loss Status Updated Date})=9,CONCAT("Q3"," ",YEAR({DE:Win/Loss Status Updated Date})),IF(MONTH({DE:Win/Loss Status Updated Date})=10,CONCAT("Q4"," ",YEAR({DE:Win/Loss Status Updated Date})),IF(MONTH({DE:Win/Loss Status Updated Date})=11,CONCAT("Q4"," ",YEAR({DE:Win/Loss Status Updated Date})),IF(MONTH({DE:Win/Loss Status Updated Date})=12,CONCAT("Q4"," ",YEAR({DE:Win/Loss Status Updated Date}))))))))))))))

Avatar

Level 3

Thanks so much for your information, Kurt. This is really helpful. I think changing from my object custom forms to a global custom form for reporting calculated fields is a good way for me to go.