6 Replies Latest reply on Jul 17, 2014 7:43 AM by Matt Lutton

# Calculate last state/product for each customer/user

Hello All,

I have a daily data about customers purchasing items/products. I want to calculate the last purchased product (or last state, etc.) for each customer. For example:

The original data:

 customer_id day item_purchased 1 2014-01-01 A 1 2014-01-05 A 1 2014-01-07 C 2 2013-05-01 A 2 2013-05-03 B 3 2014-06-04 C 3 2014-06-05 B 3 2014-06-06 A 3 2014-06-09 C 3 2014-06-12 A

The result would be:

 customer_id day item_purchased last_item_purchased 1 2014-01-01 A C 1 2014-01-05 A C 1 2014-01-07 C C 2 2013-05-01 A B 2 2013-05-03 B B 3 2014-06-04 C A 3 2014-06-05 B A 3 2014-06-06 A A 3 2014-06-09 C A 3 2014-06-12 A A

I tried to use last() and index() functions, but couldn't figure it out. In SQL, this would be a self join on max(day), but I don't think that a blending would be the solution. Can I do this with one table calc?

Thank you,

• ###### 1. Re: Calculate last state/product for each customer/user

Is the second table the actual output you want, or are you trying to do something else once you get the results?  I ask, because the approach may differ depending on the end-goal for the view.  Please let us know if the output of your second table is the true end goal, or if you're attempting to build some other view from the results?

• ###### 2. Re: Calculate last state/product for each customer/user

Well, the following uses a Nested Table Calculation to return the results you have in your mockup.

The first step was to identify the LAST item purchased within a Customer ID partition.  I did this using the "Last Item Purchased" table calc:

IF LAST()==0 THEN attr([Item Purchased]) end

With the proper compute using settings, this returns NULL for all other rows and the Item Purchased in the last row for each Customer ID partition.

The last step is to return that for all rows within the partition.  I did this using a simple WINDOW_MAX([Last Item Purchased]), with the same compute using settings, partitioning on Customer ID, addressing on everything else.

In the attached, I left the Last Item Purchased Calc in the view, but you can remove it and the Nested Calculation will still work.

I hope this helps--cheers!

• ###### 3. Re: Calculate last state/product for each customer/user

Hi Matthew,

I would like to have the second table without the item_purchased column. So for each valid customer-day pair the last_item_purchased column:

 customer_id day last_item_purchased 1 2014-01-01 C 1 2014-01-05 C 1 2014-01-07 C 2 2013-05-01 B 2 2013-05-03 B 3 2014-06-04 A 3 2014-06-05 A 3 2014-06-06 A 3 2014-06-09 A 3 2014-06-12 A

Thanks,

• ###### 4. Re: Calculate last state/product for each customer/user

I posted a possible solution above--you can remove the "Last Item Purchased" pill from the Rows shelf, and the Nested Table Calc will still work properly, as long as the Compute Using for both calculations is set properly within the Nested Calc:

• ###### 5. Re: Calculate last state/product for each customer/user

Thank you, this is exactly what I needed.

• ###### 6. Re: Calculate last state/product for each customer/user

No problem, thanks for the challenge!  This helped me connect a few dots in my own mind, as well.  Cheers!