Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a projects with 6 sub projects say for eg. Project1 has subprojects1, subprojects2, subprojects3, subprojects4, subprojects5, subprojects6, subproject7, subprojects8 and subprojects9
Every sub projects should have to be completed after the previous subproject.
Project,SP,FinishDate(MM/DD/YYYY)
P1,SP1, 02/02/2019
P1,SP2, 02/04/2019
P1,SP3, 02/06/2019
P1,SP4, 02/12/2019
P1,SP5, 02/14/2019
P1,SP6, 02/13/2019
P1,SP7, 02/16/2019
P1,SP8, 02/20/2019
P1,SP9, 02/22/2019
Here SP6 finished before the SP5 finish date by 1 day. So I want to highlight the subproject SP6 and Project P1.
Good, that means we're almost there. You should create another IF with:
IF(Above(Project) <> Project, Black(), IF(.... [next one]
Because every first one should be black of course.
Jordy
Climber
Hi Rammuthiah,
Use this in the coloring part of your tables' field:
IF(Above Floor(Date#(FinishDate,'MM/DD/YYYY')) < Floor(Date#(FinishDate,'MM/DD/YYYY')), Black(),Red())
The floor and date# are used for the easiness of comparing the values. This can go wrong with date formats.
Jordy
Climber
If it is for single project, your code is fine. I am looking for several projects with subprojects. therefore I have to group it based on project
No problem, then we extend it:
IF(Above(Project) = Project and Above Floor(Date#(FinishDate,'MM/DD/YYYY')) > Floor(Date#(FinishDate,'MM/DD/YYYY')), Red(),Black())
Jordy
Climber
Hi Jordy,
Thank you for your valuable reply. Above function not working here for my scenario. While tried above(), it changes to null values. I hope because of not ordering the Project/Subprojects
Hmm the sort shouldn't matter in this case. Are you sure that all your values are filled in the model? Can you maybe send a sample QVF?
Jordy
Climber
Plz find qvf and data used for it
Data used and status is the correct output which has to be seen in QS output
In the script it's even easier!
See the attachment for the solution. This is also more stable and not depended on your sorting.
Jordy
Climber
Thanks Jordy. I slightly modified the data which based on my requirement. P1 has some missing subprojects and P2 has some null dates. Is it possible checking for all dates of sub projects within project?
Project | Sub Project | Finish Date |
P1 | SP1 | 02/02/2019 |
P1 | SP2 | 02/04/2019 |
P1 | SP3 | 02/06/2019 |
P1 | SP6 | 02/13/2019 |
P1 | SP7 | 02/16/2019 |
P1 | SP8 | 02/20/2019 |
P1 | SP9 | 02/22/2019 |
P2 | SP3 | 02/06/2019 |
P2 | SP4 | 02/12/2019 |
P2 | SP5 | |
P2 | SP6 | |
P2 | SP7 | 02/04/2019 |
P2 | SP8 | 02/20/2019 |
P2 | SP9 | 02/22/2019 |
P3 | SP1 | 02/02/2019 |
P3 | SP2 | 02/04/2019 |
P3 | SP3 | 02/06/2019 |
P3 | SP4 | 02/12/2019 |
P3 | SP5 | 05/14/2019 |
P3 | SP6 | 05/15/2019 |
P3 | SP7 | 05/16/2019 |
P3 | SP8 | 05/17/2019 |
P3 | SP9 | 05/18/2019 |