Two date field comparision in set analysis

[manojkumargowda]

I want to get count of all records whose due date is lesser or equal to end date. I'm using below expression but it is not working. Please suggest.

=Count({<Duedate={"<Enddate"}>}Id) 

[kaushiknsolanki · Accepted solution]

Hi,

The best & optimized way to do this is to create a flag in script which will tell you record by record if the date is equal or less then the other date.

Then use this flag in set analysis to filter those records.

Regards,

Kaushik Solanki

[manojkumargowda]

Thanks Kaushik,

I have one more question regarding set analysis. what if I wanted to get count of records with below condition.

status='close' and (tested='OK' or len(tested)=0)

[Anil_Babu_Samineni · Accepted solution]

There are many ways, AS always script flag is powerful of your requirement

Load If(Duedate<Enddate, 'Before End Date', 'Remain') as Flag;

Expression Approaches

using Flag --> Count({<Flag = {'Before End Date]}>} Id)

Direct - (i) --> Count(If(Duedate<Enddate, Id))

Direct - (ii) --> Count({<Duedate = {"=Num(Duedate)<Num(Enddate)"}>} Id) // this won't work in KPI

[Anil_Babu_Samineni]

Could be this?

Count({<tested={'OK'}, status={'close'}>+<tested={"=len(tested)=0"}, status={'close'}>} Id)