Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
manojkumargowda
Creator
Creator

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
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

Anil_Babu_Samineni

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

 

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)

View solution in original post

4 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
manojkumargowda
Creator
Creator
Author

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

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

 

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
Anil_Babu_Samineni

Could be this?

Count({<tested={'OK'}, status={'close'}>+<tested={"=len(tested)=0"}, status={'close'}>} Id)
Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)