Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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]))
This worked perfectly. Thank you Sunny!
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.
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?
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]))
I would like it to do the opposite. Ignore any selection of the Inquiry Date and the fields listed above.
Right now, it should be ignoring any selection in the fields listed above. Have you given it a try?
Yes - oddly enough it isn't ignoring any of the fields.
Is there a way to share a sample?