topic Re: Set Analysis - Last 90 Days in App Development

Set Analysis - Last 90 Days

joshakehurst — Tue, 29 May 2018 18:01:32 GMT

I'm attempting to use Set Analysis in Qlik Sense to filter a date range based on the last 90 days. Using the expression below does not seem to have any effect. I've tried various combinations, but just can't get anything to work. The dimension in this scenario is Interaction Start Date Local.

=Avg({$<[Interaction Start Date Local]=${">=today()-90"}>}Duration)

Re: Set Analysis - Last 90 Days

marcginqo — Tue, 29 May 2018 18:08:25 GMT

Josh,

your syntax does not seem to be correct.

Try the following:

Avg({$<[Interaction Start Date Local]={">=$(=Today()-90)"}>}Duration)

Marc

Re: Set Analysis - Last 90 Days

sunny_talwar — Tue, 29 May 2018 18:10:33 GMT

How about this?

=Avg({$<[Interaction Start Date Local]=${">=$(=Date(Today()-90))"}>} Duration)

Re: Set Analysis - Last 90 Days

joshakehurst — Tue, 29 May 2018 18:20:26 GMT

Thanks for the tip, but no matter how I modify this expression Qlik basically ignores the parameter for date range (-30, -60, etc.). I'm at loss on how to make this work.

Re: Set Analysis - Last 90 Days

marcginqo — Tue, 29 May 2018 18:30:26 GMT

Josh,

make sure you don't have any selections made on the date field in the set analysis expression. Otherwise it won't work. To avoid this you can add the following to you set analysis.

Avg({$<[Interaction Start Date Local]={">=$(=Today()-90)"},[Interaction Start Date Local]=>}Duration)

The extra addition for the Interaction Start Date Local is to make sure the selections on this field are ignored.

Marc

Re: Set Analysis - Last 90 Days

sunny_talwar — Tue, 29 May 2018 18:30:56 GMT

Are you making in another date and time related field? such as Month?

Re: Set Analysis - Last 90 Days

marcginqo — Tue, 29 May 2018 18:33:03 GMT

And what is the format of you date? It might be that you have to convert it to a date. When using expressions like Today()-30 it converts the date to a number. When you date field is not a number it won't work.

Try this in stead.

Avg({$<[Interaction Start Date Local]={">=$(=Date(Today()-90))"}, [Interaction Start Date Local]=>}Duration)

Re: Set Analysis - Last 90 Days

joshakehurst — Tue, 29 May 2018 18:59:00 GMT

In the data load it's defined as SET DateFormat='M/D/YYYY';

Re: Set Analysis - Last 90 Days

sunny_talwar — Tue, 29 May 2018 19:10:40 GMT

Are you making selection in another date related field?

Re: Set Analysis - Last 90 Days

joshakehurst — Tue, 29 May 2018 19:13:58 GMT

Hi Sunny - I am not making a selection on another date field.

Re: Set Analysis - Last 90 Days

sunny_talwar — Tue, 29 May 2018 19:18:05 GMT

Try this

=Avg({$<[Interaction Start Date Local] = ${">=$(=Date(Today()-90, 'M/D/YYYY'))"}>} Duration)

Assuming, Interaction Start Date Local is in the format M/D/YYYY... if it is not, then you can do this

LOAD Date([Interaction Start Date Local]) as [Interaction Start Date Local]

and then this

=Avg({$<[Interaction Start Date Local] = ${">=$(=Date(Today()-90))"}>} Duration)

Also, look here

Dates in Set Analysis

Re: Set Analysis - Last 90 Days

joshakehurst — Wed, 30 May 2018 15:09:23 GMT

Good stuff thanks for sharing. Unfortunately, nothing seems to work which is rather frustrating. Looking at our data set it would seem everything should line up correctly, but implementing the Date clause in the expression has no effect.

Re: Set Analysis - Last 90 Days

marcginqo — Wed, 30 May 2018 15:14:07 GMT

Josh,

is there a way you could send us the qvf? This way I can take a deeper look at it.

I got it working on one of my dashboards so must me a small thing.

Marc

Re: Set Analysis - Last 90 Days

sunny_talwar — Wed, 30 May 2018 15:31:53 GMT

Are you may be missing the dollar sign expansion?

=Avg({$<[Interaction Start Date Local] = ${">=$(=Date(Today()-90))"}>} Duration)

Re: Set Analysis - Last 90 Days

joshakehurst — Wed, 30 May 2018 15:42:24 GMT

Unfortunately I can't share the qvf file due to data / privacy restrictions.

Re: Set Analysis - Last 90 Days

joshakehurst — Wed, 30 May 2018 15:43:24 GMT

Unfortunately I can't share the qvf file due to data / privacy restrictions. Thanks for the offer though.

Re: Set Analysis - Last 90 Days

joshakehurst — Wed, 30 May 2018 15:44:23 GMT

Sunny - I tried adding the $ as described, but nothing changed.

Re: Set Analysis - Last 90 Days

sunny_talwar — Wed, 30 May 2018 15:46:56 GMT

Would you be able to share the same image like above with this expression

=Avg({$<[Interaction Start Date Local] = {">=$(=Date(Today()-90, 'M/D/YYYY'))"}>} Duration)

Re: Set Analysis - Last 90 Days

joshakehurst — Fri, 01 Jun 2018 15:20:01 GMT

Thanks this works!