13 Replies Latest reply: Apr 22, 2016 3:02 PM by Sunny Talwar

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

-Jason

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

• ###### 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.

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

• ###### 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?

• ###### 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?

• ###### 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.

• ###### Re: Ignore Any Selection

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

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.