Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Highlighted
Not applicable

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

13 Replies
Highlighted

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

Highlighted
Not applicable

This worked perfectly. Thank you Sunny!

Highlighted

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.

Highlighted
Not applicable

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?

Highlighted

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

Highlighted
Not applicable

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

Highlighted

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

Highlighted
Not applicable

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

Highlighted

Is there a way to share a sample?