Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

manojkumargowda
Contributor

Two date field comparision in set analysis

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) 

Labels (1)
2 Solutions

Accepted Solutions

Re: Two date field comparision in set analysis

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

Re: Two date field comparision in set analysis

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

 

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
4 Replies

Re: Two date field comparision in set analysis

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
Contributor

Re: Two date field comparision in set analysis

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)

Re: Two date field comparision in set analysis

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

 

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: Two date field comparision in set analysis

Could be this?

Count({<tested={'OK'}, status={'close'}>+<tested={"=len(tested)=0"}, status={'close'}>} Id)
Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)