Show only the month with the most recent purchase

[ivanoski]

Greetings all,

I am trying to make a picot table show only the month with the most recent purchase of a customer. 

In this screenshot i have two customer_id but i want to show their most recent purchase only. 

This is so that i can select customer_id s within a month that shows their most recent purchase 

I am trying to see how many customers made their most recen purchases in november for example who have not returned to make a purchase.

Purchase is tied directly to date, so the =max(DATE)  shows their most recent purchase. but when i add the month dimension, it shows their historic purchases per month, I only want to see the most recent month. 

I have done this in a straight table but i need it in a picot table to count the customers within that particular month..

Please help, thanks.

[TimvB]

You could add an extra dimension field to tag the most recent purchase. This can be done in the script as follows:

MaxCustomerOrderDate_map:

Mapping Load

%CustomerID

Max(OrderDate)  as MaxOrderDate

Resident OrderFacts

Group by %CustomerID;

NewOrderFacts:

NoConcatenate Load

*,

If(OrderDate = ApplyMap('MaxCustomerOrderDate_map', %CustomerID), '1', '0') AS MostRecentPurchase_Tag

Resident OrderFacts;

Drop table OrderFacts;

Raname Table NewOrderFacts to OrderFacts;

In the pivot table, just use the following expression to count the customers that made their last purchase in that particular month:

count({<MostRecentPurchase_Tag = {'1'}>} MostRecentPurchase_Tag)

Hope it helps!