Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Ignore Any Selection

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

1 Solution

Accepted Solutions
Not applicable

Re: Ignore Any Selection

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.

13 Replies

Re: Ignore Any Selection

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

Not applicable

Re: Ignore Any Selection

This worked perfectly. Thank you Sunny!

Re: Ignore Any Selection

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.

Not applicable

Re: Ignore Any Selection

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

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

Not applicable

Re: Ignore Any Selection

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

Re: Ignore Any Selection

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

Not applicable

Re: Ignore Any Selection

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

Re: Ignore Any Selection

Is there a way to share a sample?