Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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.
Is it like this problem?
https://community.qlik.com/t5/Qlik-Sense-App-Development/Nested-sales/m-p/1548336#M39473
-Rob
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.
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
Hey Rob,
Again, thank you for your help with this.
The fields I'm working with are:
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)>})
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