Set Analysis with a Dynamic Dates

[RichardSmith]

Hi, 

Not sure how to clearly word this question, but is there a way of using set analysis with dates so that for each row of the table, the date range changes. e.g.

Count({$<Order_Date={"<=$(=WeekEndDateLess31Days))"}>}distinct(ORDER_NO))

I don't want WeekEndDateLess31Days to be a variable as it's a value which changes depending on the data selected.

Because there are multiple occurrences of WeekEndDateLess31Days, it shows as null and fails.

Max(WeekEndDateLess31Days) gives the max date, but this isn't what is required. 

Thanks

[Or · Accepted solution]

This isn't really a question anyone but you can answer. You need to determine how to narrow down your multiple dates into a single date that feeds into the set analysis, in a way that meets your requirements. If you can't do that, you can't use set analysis, and you'll need to instead use logic within the function, e.g. count(distinct if(something,ORDER_NO))

[RichardSmith]

Thanks for taking the time to reply. That makes sense so will aim for that. I had also tried the if approach without success, but hopefully can code something as you've advised. Thank you