Double Date Range Filters

[sgagliardi]

Is it possible to produce a double date range filter whereby I could first filter down to an initial date range, say 1/2017-12/2017, returning "occurrences" within that range, and then further filter those occurrences for all other occurrences happening within a buffer period (of let's say 3 months) in either direction of the assessed occurrence? I'm working with a peculiar use case where something like this may be necessary.

[rwunderlich]

Yes. You could use a set expression that expands the min & max currently selected dates by 90 days, or three months. Something like this for 90 days:

sum({<Year=,OrderDate={">=$(=date(min(OrderDate)-90))<=$(=date(max(OrderDate)+90))"}>}LineSalesAmount)

and for 3 Months:

sum({<Year=,OrderDate={">=$(=AddMonths(min(OrderDate),-3))<=$(=AddMonths(max(OrderDate),3))"}>}LineSalesAmount)

The Year= is there as an example of ignoring other time related fields.  I am expanding only one field, OrderDate, and don't want other time fields to limit my selection. 

 -Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[sgagliardi]

Hey Rob,

Thanks for your response! This seems along the lines of what I'm looking for, though I'm having some trouble getting it to work for me. Allow me to better explain my scenario.

In simplest terms, let's say I have a 2-column table of data that contains a list of patient medical procedures, and the dates on which they occurred. What I want to do is filter down to a specific procedure, let's say hip replacement, and a date range between which I want to analyse this procedure. Easy enough, I simply use a procedure filter to filter for just hip replacements, and a MonthYear filter to select the desired set of months I want to look into. With these selected, I now want to return all other procedures (not just hip replacements) occurring within select-able thresholds before an after the date on which the procedure took place. I'm finding that in Qlik, this conflicts with both of my earlier established filters, procedure, and MonthYear, as I'm now asking Qlik to return results from outside of that initial query.

[rwunderlich]

Is it like this problem?

https://community.qlik.com/t5/Qlik-Sense-App-Development/Nested-sales/m-p/1548336#M39473

-Rob

[sgagliardi]

Hey Rob,

It's a little similar, though instead of returning a sum of sales or transactions for a customer making a specific purchase or sequence of purchases, I'd want to see what all of those other purchases are.

So putting into customer and product sales terms:

I filter for a product, and I filter for a date range, and get all sales of that products within that date range. What I want to do now is find all other products bought by those same customers during a second threshold of time, let's say 3 months before and after the purchase of the original product I'm analyzing.

[rwunderlich]

So you'll combine the set logic from this thread and the other I referenced.  Sets have to be used in aggregation function, but you can use the function only() as in only(ProcedureId) if you want to list all the purchases.  Give it a shot but if you need some help with the syntax at this point provide your actual field names and we can move away from the sales analogy. 

-Rob

[sgagliardi]

Hey Rob,

Again, thank you for your help with this.

The fields I'm working with are:

• MonthYear - ex: Jan-2017
• AdmitDate - Date of patient admission
• PatNo - Patient Number
• ProcCode - Procedure code

The table might look something like:

PatNo     AdmitDate     ProcCode

1234        02/17/2017    000407

5678        03/12/2017    000407

9012        03/10/2017    000407

1234        01/17/2017    000312

5678        04/12/2017    000990

9012        05/10/2017    000100

1234        06/17/2017    000567

5678        10/12/2016    000213

9012        04/10/2017    000143

1234        03/17/2017    000789

5678        12/12/2016    000732

9012        02/10/2017    000332

1234        01/01/2018    000222

5678        07/12/2017    000807

9012        12/10/2016    000110

What I would want to do is first filter for a specific ProcCode, and then have a separate filter that will return all other procedures occurring (PatNo entries) within an establish-able number of months of the AdmitDate for the same set of PatNo returned from my initial ProcCode filtering.

So from the example table above, a scenario could be:

I use [ProcCode] filter to analyse ProcCode 000407, returning:

PatNo     AdmitDate     ProcCode

1234        02/17/2017    000407

5678        03/12/2017    000407

9012        03/10/2017    000407

I would want to use this new filter to see all other procedures for these PatNo for 3 months before, and 6 months after (configurable by user) the AdmitDate, returning:

PatNo     AdmitDate     ProcCode

1234        01/17/2017    000312

1234        06/17/2017    000567

1234        03/17/2017    000789

5678        04/12/2017    000990

5678        07/12/2017    000807

9012        05/10/2017    000100

9012        02/10/2017    000332

9012        04/10/2017    000143

I've attempted to combine the two solutions you had suggested, populating it with my own data, though my syntax and expertise here is intermediate at best.

=sum({<MonthYear=,AdmitDate={">=$(=AddMonths(min(AdmitDate),-6))<=$(=AddMonths(max(AdmitDate),6)"}>},{<PatNo=P({<ProcCode=P(ProcCode), PatNo=>} PatNo)>})

[rwunderlich · Accepted solution]

How about:

sum({<
ProcCode=E(ProcCode),PatNo=P(PatNo),
AdmitDate={">=$(=AddMonths(Min(TOTAL AdmitDate),-2))<=$(=AddMonths(Max(TOTAL AdmitDate),6))"}
>}1)

Sample attached. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com