topic Ignore Any Selection in QlikView

Ignore Any Selection

Thu, 21 Apr 2016 18:19:26 GMT

I have the below expression where I am calculating the min count of email address in 60 days but I can't seem to figure out how to ignore any selections on the date fields?

=min(aggr(count({$<[Inquiry Date] = {">=$(=Date(Max([Inquiry Date])-60)<=(=Date(Max([Inquiry Date]))))"},[Inquiry Date]=,[Inquiry Week]=, [Inquiry Month]=, [Inquiry Year]=>}[Email Address]),[Inquiry Date]))

Thanks in advance!

-Jason

Re: Ignore Any Selection

sunny_talwar — Thu, 21 Apr 2016 18:20:51 GMT

May be this:

=Min({<[Inquiry Date], [Inquiry Week], [Inquiry Month], [Inquiry Year]>} Aggr(Count({$<[Inquiry Date] = {">=$(=Date(Max([Inquiry Date])-60))<=$(=Date(Max([Inquiry Date])))"}, [Inquiry Date], [Inquiry Week], [Inquiry Month], [Inquiry Year]>}[Email Address]),[Inquiry Date]))

Re: Ignore Any Selection

Thu, 21 Apr 2016 18:23:27 GMT

This worked perfectly. Thank you Sunny!

Re: Ignore Any Selection

sunny_talwar — Thu, 21 Apr 2016 18:25:25 GMT

Awesome

I was still looking to make sure if set analysis was in place and found couple of issues and fixed those as well. Not sure if this was a typo or you might need to fix those in your application as well.

Re: Ignore Any Selection

Fri, 22 Apr 2016 06:43:43 GMT

Actually - i checked again and it doesn't work as it is showing me an all time Min (0) instead of a 60 day min (140). I think the [Inquiry Date] is somehow cancelling each other out somewhere?

Re: Ignore Any Selection

sunny_talwar — Fri, 22 Apr 2016 07:49:52 GMT

So you do want to see it changed based on the certain selection in Inquiry Date? Can you try this?

=Min({$<[Inquiry Date] = {">=$(=Date(Max([Inquiry Date])-60))<=$(=Date(Max([Inquiry Date])))"}, [Inquiry Week], [Inquiry Month], [Inquiry Year]>} Aggr(Count({$<[Inquiry Date] = {">=$(=Date(Max([Inquiry Date])-60))<=$(=Date(Max([Inquiry Date])))"}, [Inquiry Week], [Inquiry Month], [Inquiry Year]>}[Email Address]),[Inquiry Date]))

Re: Ignore Any Selection

Fri, 22 Apr 2016 15:52:33 GMT

I would like it to do the opposite. Ignore any selection of the Inquiry Date and the fields listed above.

Re: Ignore Any Selection

sunny_talwar — Fri, 22 Apr 2016 16:07:00 GMT

Right now, it should be ignoring any selection in the fields listed above. Have you given it a try?

Re: Ignore Any Selection

Fri, 22 Apr 2016 16:25:40 GMT

Yes - oddly enough it isn't ignoring any of the fields.

Re: Ignore Any Selection

sunny_talwar — Fri, 22 Apr 2016 16:27:46 GMT

Is there a way to share a sample?

Re: Ignore Any Selection

Fri, 22 Apr 2016 18:43:50 GMT

Unfortunately not at the moment.

I think I finally cracked it! The date(max([Inquiry Date])) that was nested inside the set analysis was causing the max and max-60 date to fluctuate. So I did this:

Min({$<[Inquiry Date] = {">=$(=Date(Max({<[Inquiry Date], [Inquiry Week], [Inquiry Month], [Inquiry Year]>}[Inquiry Date])-60))<=$(=Date(Max({<[Inquiry Date], [Inquiry Week], [Inquiry Month], [Inquiry Year]>}[Inquiry Date])))"}, [Inquiry Week], [Inquiry Month], [Inquiry Year]>} Aggr(Count({$<[Inquiry Date] = {">=$(=Date(Max({<[Inquiry Date], [Inquiry Week], [Inquiry Month], [Inquiry Year]>}[Inquiry Date])-60))<=$(=Date(Max({<[Inquiry Date], [Inquiry Week], [Inquiry Month], [Inquiry Year]>}[Inquiry Date])))"}, [Treatment Code]={'*'},[Treatment Name]={'*'},[Inquiry Week], [Inquiry Month], [Inquiry Year]>}[Email Address]),[Inquiry Date]))

Now it completely ignores any of the date fields

I wonder if there is an easier/simpler approach to this.

Re: Ignore Any Selection

sunny_talwar — Fri, 22 Apr 2016 18:47:40 GMT

May be you want to ignore not just the date fields, but all selections. If that's true then this can work:

Min({1<[Inquiry Date] = {">=$(=Date(Max({1}[Inquiry Date])-60))<=$(=Date(Max({1>}[Inquiry Date])))"}>} Aggr(Count({1<[Inquiry Date] = {">=$(=Date(Max({1>}[Inquiry Date])-60))<=$(=Date(Max({1}[Inquiry Date])))"}, [Treatment Code]={'*'},[Treatment Name]={'*'}>}[Email Address]),[Inquiry Date]))

Re: Ignore Any Selection

Fri, 22 Apr 2016 18:50:38 GMT

That's a good suggestion and one I'll definitely keep for the books!

In this instance I need all other field selections to work except date fields.

Re: Ignore Any Selection

sunny_talwar — Fri, 22 Apr 2016 19:02:58 GMT

Just to give another suggestion, you can use the trick, i specified here:

Re: Detaching Individual Selections