How to calculate the TIME of last ORDER

[uacg0009]

Hi all,

I have the data like below and also have a list of ITEM:

The ORDER is 1 to n order by the TIME, now what I want to calculate is:

if I choose C in the list of ITEM, I want to calculate the TIME of last ORDER of the VIP who purchased C.

it also means that I want to know what the TIME that corresponds to ORDER - 1 is.

if one VIP bought C twice or more, we choose the min(ORDER)

And at this example, the result what I want is the Table2 below:

I did a chart but the final function didn't work.

I hope you know what I want and if you have any problems pls feel free to tell me.

Thank you!!

[Anonymous]

Try

=Date(min({<ORDER=$(min(ORDER)-1)>}TIME))

[uacg0009]

Hi Michael,

I've already try it, but the result is:

it is the same with the min(TIME) and the $(min(ORDER)-1) don't work.

Do you have any other method?

Thank you!

[Anonymous]

I'm a little confused by your question though I'm tired so it may be entirely me.

Does =Date(aggr(min(TIME),VIP,ORDER)) get you any closer to what you want?

[uacg0009]

Hi Michael,

In this example, I just want to get the TIME of four situations,

VIP=001 and ORDER=2

VIP=002 and ORDER=0

VIP=003 and ORDER=1

VIP=004 and ORDER=1

And because ORDER won't be 0, so there will be no TIME in the column of VIP=002.

[#6]

PFA

[uacg0009]

Hi Palla,

Thank you so much, but do you have any other method to make it in expression, not in script?

[tresesco · Accepted solution]

It would be bit complex. PFA. Hope this would help you.

[uacg0009]

Hi tresesco,

Thank you so much! And sorry for the late reply, it is very helpful for me.

But just one probelm, actually my VIP data is too large, so my chart is allocated memory exceeded.

[Anonymous]

Hey Changjun,

It is best practice to perform as much of the expression as you can in the script.  Otherwise you will run into performance issues in your application.