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: 
Not applicable

Date Filter in Set Analysis

HI

Using Qlik Sense

I have an expression

COUNT ({<[Current Status] = {"Awaiting"}>}[Claim Ref]) And [Current Status Date] = Date(Today(-14))

So what I need the expression to do is locate a claim where the status is Awaiting and the date in the Current status date column is from 14 days ago or older.

Any help would be much appreciated

Thanks in advance 

1 Solution

Accepted Solutions
Not applicable
Author

I have Solved this Now I used some logic from what you posted Gysbert Wassenaar and some from other sources the expression is now:

Count ({<[Status] = {" Awaiting"}>}If ([Current Status Date] < Date(Today()-26),0)) 

this shows everything that is 26 days and older

Cheers

View solution in original post

7 Replies
Gysbert_Wassenaar

Try: COUNT ({<[Current Status] = {"Awaiting"}, [Current Status Date] = {'<=$(=Today()-14)'}>}[Claim Ref])


talk is cheap, supply exceeds demand
Not applicable
Author

Hi

Unfortunately this does not return any data it displays the number as 0 but I know there are at least 100 Claims that fit the criteria.

I also tried to use an IF statement for this but with no luck

Thanks 

Gysbert_Wassenaar

Post a qlikview document that demonstrates the problem.


talk is cheap, supply exceeds demand
Not applicable
Author

I don't have a document I can show due to data protection. If it makes any difference all the data is pulled from a Spreadsheet in to Qlik Sense.

Could an expression like

Sum(Aggr(If([Current Status Date]) > Date(Today()-14),1,0)) [Current Status] = {"Awaiting})

Or something similar I'm pretty new to Qlik and not great at the syntax

Not applicable
Author

StatusCurrent Status Date Status Month
Awaiting23/8/158/15
Awaiting28/8/158/15
Awaiting11/5/155/15
Ended5/9159/15
Ended8/10/1410/14
Ended6/9/139/13
Ended23/12/1412/14
Pending 11/9/159/15

Hi this is an example of what I have to work with so what I want is

If Status = "Awaiting" and Date is today()-14 or older then Count it. 

Gysbert_Wassenaar

The expression I posted should work if your field [Current Status Date] actually contains date values and not string values that only look like dates. If that field contains string values then you need to use the date# function in the script to turn those strings into dates:

LOAD

     ...some fields...,

     date#([Current Status Date],'D/M/YY') as [Current Status Date],

     ...some more fields...

FROM ...


talk is cheap, supply exceeds demand
Not applicable
Author

I have Solved this Now I used some logic from what you posted Gysbert Wassenaar and some from other sources the expression is now:

Count ({<[Status] = {" Awaiting"}>}If ([Current Status Date] < Date(Today()-26),0)) 

this shows everything that is 26 days and older

Cheers