How to calculate the time of last purchase

[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 last purchase TIME 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:

001 2013-02-01

003 2012-11-01

004 2013-01-22

something like above.

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

Thank you!!

[tresesco]

May be like attached sample?

[Sokkorn]

Hi Changjun,

Can you manipulate result set that you want? Let say, if we select C then what kind of result that you want to display. The one that you provide is not understand.

Regards,

Sokkorn

[uacg0009]

Thank you tresesco, sorry the attached file is not correct last time. And let me to clarify what I want, if we select ITEM

= C, the result what I want is:

Because for VIP=001, when ITEM = C, ORDER = 3, so I want to calculate the TIME that ORDER=3-1,

so we output     001 B 2 2012-02-01           .

when VIP=002 and ITEM=C, the ORDER=1, because the ORDER start from 1,so there is no ORDER=0, so we don't

output VIP=002.

when VIP=003 and ITEM=C, the min(ORDER)=2, so we output the TIME that min(ORDER)=2-1,

so we output     003 B 1 2012-11-01

I think the VIP=004 is the same with VIP=001,

I hope I have clarified what I want and if you have any problems pls tell me.

Thank you~~

[uacg0009]

Thank you Cheav, sorry the attached file is not correct last time. And let me to clarify what I want, if we select ITEM

= C, the result what I want is:

Because for VIP=001, when ITEM = C, ORDER = 3, so I want to calculate the TIME that ORDER=3-1,

so we output     001 B 2 2012-02-01           .

when VIP=002 and ITEM=C, the ORDER=1, because the ORDER start from 1,so there is no ORDER=0, so we don't

output VIP=002.

when VIP=003 and ITEM=C, the min(ORDER)=2, so we output the TIME that min(ORDER)=2-1,

so we output     003 B 1 2012-11-01

I think the VIP=004 is the same with VIP=001,

I hope I have clarified what I want and if you have any problems pls tell me.

Thank you~~

[DavidFoster1]

I would suggest looking at the bottom function:

bottom([ total ] expression [ , offset [,n ]])

Returns the value of expression evaluated with the chart's dimension values as they appear on the last row of the current column segment in a table or, in the case of bitmap charts, in the chart's straight table equivalent.

If the chart is one-dimensional or if the expression is preceded by the total qualifier, the current column segment is always equal to the entire column.

If the table or table equivalent has multiple vertical dimensions, the current column segment will include only rows with the same values as the current row in all dimension columns except for the column showing the last dimension in the inter field sort order. The inter field sort order for pivot tables is defined simply by the order of the dimensions from left to right. For other chart types this can be manipulated in the Chart Properties: Sort dialog.

Specifying an offset greater than 1 lets you move the evaluation of expression up to rows above the bottom row. A negative offset number will actually make the bottom function equivalent to a top function with the corresponding positive offset number. Recursive calls will return NULL.

By specifying a third parameter n greater than 1, the function will return not one but a range of n values, one for each of the last n rows of the current column segment. In this form, the function can be used as an argument to any of the special Chart Range Functions.

Examples:

bottom( sum( Sales ))

bottom( sum( Sales ), 2 )

bottom( total sum( Sales ))

The offset parameter sounds like it will also satisfy your -1 requirement.

[uacg0009]

Thank you Foster, I have already read the description of Bottom() function. But I don't know how to use it in my

example, Do you have some ideas?