Highlight misorder in Date

[rammuthiah]

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.

[JordyWegman]

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

[rammuthiah]

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

[JordyWegman]

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 

[rammuthiah]

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

[JordyWegman]

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

[rammuthiah]

Plz find qvf and data  used for it

[rammuthiah]

Data used and status is the correct output which has to be seen in QS output

[JordyWegman]

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

[rammuthiah]

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