Expression setting to compare product popularity

[yooyoo]

Hi there,

I would like to run a study to see model is popular as customer's 2nd order in 2024, but I just don't have idea to set the expression. Wondering if anyone may give me some insights. 

The field I have: [Customer ID], [Order Number], [Order Status], [Order Date], [Model]

Goal: to check 1st order status as "Finished" and 2nd order happens in 2024; the customer 1st order doesn't have to be 2024.

I was trying to use the below expression but nothing popped up......

count(
distinct
aggr(
if(
rank(total [Order Date], 1, [Customer ID]) = 2 and
year([Order Date]) = 2024 and
if(rank(total [Order Date], 1, [Customer ID]) = 1, [Order Status]) = 'Finished',
[Order Number]
),
[Customer ID], [Order Number]
)
)

Thank you

[Kushal_Chawda]

@yooyoo  what is your end goal? What kind of report or graph you need? Do you need just second order in 2024 with status finished or all the order in 2024 without looking at status?

[yooyoo]

Hello Kushal, I would like to run a table to get the year 2024- 2nd order model list with descending no. of orders, 1st order can be placed before/ in 2024 as long as the order status is "Finished". 

Thank you for your help.

[Kushal_Chawda]

@yooyoo  Assuming you have Model in dimension try below expression. I would advise to use script flags which will make expression bit more simpler. Also I have used today() function instead of hardcoding 2024.

 count(distinct
aggr(
       if( OrderDate=
       min(total <Model,CustID>{<OrderDate={">=$(=yearstart(today()))<=$(=yearend(today()))"}, CustID =  p({1<Status={'Finished'}, OrderDate={"<$(=yearstart(today()))"}>})>} OrderDate),

       OrderID),

Model,CustID,OrderID)

)

[yooyoo]

Hi Kushal, thanks for your reply. I don't quite understand the usage of min part, would you mind explain the usage of min in the expression? Thank you so much. 

[Kushal_Chawda]

@yooyoo   Below part of expression generates all possible CustID who placed order, now from these custid we need to identify second order placed which could be nothing but Identifying very first order in 2024 which can be identify with Min(Date)-first date

CustID =  p({1<Status={'Finished'}, OrderDate={"<$(=yearstart(today()))"}>})