Solved: Ignore Any Selection - Qlik Community

Report Inappropriate Content · ‎2016-04-21

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

Report Inappropriate Content · ‎2016-04-22

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.

View solution in original post

sunny_talwar · ‎2016-04-21

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]))

Report Inappropriate Content · ‎2016-04-21

This worked perfectly. Thank you Sunny!

sunny_talwar · ‎2016-04-21

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.

Report Inappropriate Content · ‎2016-04-22

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?

sunny_talwar · ‎2016-04-22

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]))

Report Inappropriate Content · ‎2016-04-22

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

sunny_talwar · ‎2016-04-22

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

Report Inappropriate Content · ‎2016-04-22

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

sunny_talwar · ‎2016-04-22

Is there a way to share a sample?

Ignore Any Selection

Related Topics