topic Set Analysis with a Dynamic Dates in App Development

Set Analysis with a Dynamic Dates

RichardSmith — Wed, 25 Jan 2023 11:15:48 GMT

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

Re: Set Analysis with a Dynamic Dates

Or — Wed, 25 Jan 2023 12:21:18 GMT

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

Re: Set Analysis with a Dynamic Dates

RichardSmith — Wed, 25 Jan 2023 13:37:42 GMT

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