topic Re: Set analysis for distinct accounts who placed their most recent order more than 60 days ago in App Development

Set analysis for distinct accounts who placed their most recent order more than 60 days ago

Number9 — Sat, 16 Nov 2024 05:10:19 GMT

Hello,

I've been getting some mixed or inaccurate results tinkering with set analysis on the expression below.

count({<[ORDERDate] = {"(=date(max(ORDERDate), 'M/D/YYYY')) <= $(=date(Today()-60, 'M/D/YYYY'))"}>}Distinct [Account ID])

The aim is to see a KPI indicating the number of distinct accounts who last placed an order more than 60 days ago. Ultimately I'll want to identify those past 90, and then past 120 days for other KPIs.

At this point I've searched through many similar questions/solutions here and online that look like they should work, but they don't. Any help is greatly appreciated.

Re: Set analysis for distinct accounts who placed their most recent order more than 60 days ago

dplr-rn — Thu, 25 Jul 2019 16:20:25 GMT

try changing your expression to below
count({<[ORDERDate] = {" <= $(=date(Today()-60, 'M/D/YYYY'))"}>}Distinct [Account ID])

Re: Set analysis for distinct accounts who placed their most recent order more than 60 days ago

Number9 — Thu, 25 Jul 2019 16:42:30 GMT

Thanks Dilipranjith,

That does return a count of distinct Account IDs that have *any* order dates, most recent or otherwise, greater than 60 days from today, but it doesn't seem to limit to the max(ORDERDate) for an Account ID.

For example: Today is 7/25/19, if Account ID=N61234 has the following ORDERDates:

7/24/19

7/1/19

6/18/19

5/21/19

4/10/19

3/3/19

then the account should not appear in the results I want, as the account's most recent ORDERDate is less than 60 days ago. It does appear in the example expression you provided.

My aim is to get a count of only those distinct accounts who last placed an order, max(ORDERDate), more than 60 days ago.

Thank you again for your time and thoughts.

Re: Set analysis for distinct accounts who placed their most recent order more than 60 days ago

dplr-rn — Thu, 25 Jul 2019 17:15:57 GMT

Ok.

that will not be possible with straightforward set analysis. you would need aggr function etc.

if you can share some sample data i can try and help.

But it Maybe simpler to mark the maximum date for each account with a flag.

Re: Set analysis for distinct accounts who placed their most recent order more than 60 days ago

Number9 — Thu, 25 Jul 2019 17:53:47 GMT

There isn't a way to handle this with a nested set analysis? Seems strange to me that this can't work straightforwardly outside of a load script. My data set is large, and changing daily as new orders come in. I'll look to provide a sample in a bit. Thanks again.

Re: Set analysis for distinct accounts who placed their most recent order more than 60 days ago

dplr-rn — Thu, 25 Jul 2019 17:57:00 GMT

it can but it would need aggr function in the nested set.

because set analysis is calculated once. so Max(Order Date) is calculated once for the entire set not per id. hence the need for aggr function

Re: Set analysis for distinct accounts who placed their most recent order more than 60 days ago

Number9 — Wed, 31 Jul 2019 14:36:53 GMT

Ultimately I just went with a max flag field in my load script as suggested by Dilipranjith:

LEFT JOIN (mytable) LOAD

[Account ID],

DATE(MAX([ORDERDate])) AS [ORDERDate],

1 AS [MAXFLAG]

RESIDENT mytable

GROUP BY [Account ID];

Then applied a very simple set analysis:

Count({<ORDERDate = {"<=$(=Date(Today()- 60,'M/D/YYYY'))"}, MAXFLAG = {"1"}>}Distinct [Account ID])

I can apply this to all date ranges for my needs. I still would prefer a set analysis solution to this particular problem, but in the end the load script works reliably for now. Thanks!