topic Re: Date Filter in Set Analysis in App Development

Date Filter in Set Analysis

Wed, 16 Sep 2015 14:25:20 GMT

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

Re: Date Filter in Set Analysis

Gysbert_Wassenaar — Wed, 16 Sep 2015 15:13:01 GMT

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

Re: Date Filter in Set Analysis

Wed, 16 Sep 2015 15:21:28 GMT

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

Gysbert_Wassenaar — Wed, 16 Sep 2015 15:24:32 GMT

Post a qlikview document that demonstrates the problem.

Re: Date Filter in Set Analysis

Wed, 16 Sep 2015 15:45:39 GMT

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

Re: Date Filter in Set Analysis

Thu, 17 Sep 2015 09:41:23 GMT

Status	Current Status Date	Status Month
Awaiting	23/8/15	8/15
Awaiting	28/8/15	8/15
Awaiting	11/5/15	5/15
Ended	5/915	9/15
Ended	8/10/14	10/14
Ended	6/9/13	9/13
Ended	23/12/14	12/14
Pending	11/9/15	9/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

Gysbert_Wassenaar — Thu, 17 Sep 2015 17:22:44 GMT

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 ...

Re: Date Filter in Set Analysis

Fri, 18 Sep 2015 10:21:48 GMT

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