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

[Number9]

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.

[dplr-rn]

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

[Number9]

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.

[dplr-rn · Accepted solution]

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.

[Number9]

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.

[dplr-rn]

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

[Number9]

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!