Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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

Re: Date Filter in Set Analysis

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

7 Replies

Re: Date Filter in Set Analysis

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


talk is cheap, supply exceeds demand
Not applicable

Re: Date Filter in Set Analysis

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 

Re: Date Filter in Set Analysis

Post a qlikview document that demonstrates the problem.


talk is cheap, supply exceeds demand
Not applicable

Re: Date Filter in Set Analysis

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

Re: Date Filter in Set Analysis

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. 

Re: Date Filter in Set Analysis

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

Re: Date Filter in Set Analysis

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