Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
ivanoski
New Contributor II

Show only the month with the most recent purchase

Purchase history.png

 

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.

Labels (1)
1 Reply
Highlighted
TimvB
New Contributor III

Re: Show only the month with the most recent purchase

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!