Last transaction for each item purchased by all customers | CLEARIFY

Hello. I am new to Qqube. I have setup a report showing customer, last transaction/invoice date, item name, and used sales total and sale quantity to calculate cost per item. I want to filter so that it only shows me the last (most recent) purchase of every item for each customer. For example, I have customers who buy the same item once per week and have for the last 5 years. I don't want to see every single one of those purchases, I just want to see the last time they purchased and the cost at which they purchased it. I can't figure out how to filter for LAST date of transaction for each item.

Our goal is to pick-up "lost" customers.  To easily find customers who have not puchased in a long time.  We could use the same table, as described above, to sort by item price to call customers whose price we can improve.  All goals are to generate more sales!

If there is another solution to achieve the goal stated above, I don't have to have it they way I set it up.  Any advice is appreciated!

Hi Morgan,

It depends on what tool you're using to access the data. Since we only use Crystal Reports, I would suggest this:

Create a group on customer, a group on item name, add a Summary field for the Txn Date (Maximum) and a group on Txn Date, and then set the Group Sort to Top 1 for each Maximum Date. Sort the detail rows by date, and place the fields you want to show in the Group 3 footer (by date).  Of course I'd have to try that to see exactly how to make it work - but that's my quick answer. Fran Reed could answer for using Excel!

Alternately you could write some fancy SQL :)

Hey there...  in Excel, I would create a pivot report with ITEM first, then Customer, Last OrderDate(or ref number).. 

When on a pivot table, Excel should do the balance of the grouping/filtering for you.

On the "listing" tab of QQube Sales subject,  I would add a "calculated column"  for Last Order?Y/N.  this would create a marker for the Last Order, then on your pivot , you can filter by this result..  Then pivot amounts should be correct because of the filter. 

Last Order ? would need to be some form of the MAX  (MAXIFS) function where you can find the Max(Date) for a Customer, where transaction type is "Invoice" and Item = item id in row,ref number = row ref num 

There are other approaches but this is the one that floated to the top. 

Fran

Morgan,  I just reread your query... if you are looking for "lost" sales, then you change change your view to be customer rather then item focused. Looks like you are headed towards a nice analytic that could be viewed many ways. 

On 6/12/2018 3:34 PM, Fran Reed said:

Hey there...  in Excel, I would create a pivot report with ITEM first, then Customer, Last OrderDate(or ref number).. 

When on a pivot table, Excel should do the balance of the grouping/filtering for you.

On the "listing" tab of QQube Sales subject,  I would add a "calculated column"  for Last Order?Y/N.  this would create a marker for the Last Order, then on your pivot , you can filter by this result..  Then pivot amounts should be correct because of the filter. 

Last Order ? would need to be some form of the MAX  (MAXIFS) function where you can find the Max(Date) for a Customer, where transaction type is "Invoice" and Item = item id in row,ref number = row ref num 

There are other approaches but this is the one that floated to the top. 

Fran

Hi, Fran.

I read this some time back, but had not yet started using Qqube on a regular basis.  So, thank you for the reply.  I have used Qqube to make a spreadsheet as described above and made a pivot table filtering by customer name, with items and transaction dates in rows.  I did this because I couldn't figure out how to get the date as a column or a value without it turning it into a "count", which was useless; I need the actual date.

Anyhow, it works as it is.  Now my problem is the filter for MAX transaction.  I understand what you are suggesting, but I can't figure out how to get the program to do it.  I am using the latest release of Excel, though I have not yet updated Qqube to the current version.  Could you - or anyone else reading this! - give me a few more details on how to do the MAX formula to filter for the most recent date only?

Thanks,

Morgan

Morgan,

Be happy to help you out.  Would be able to send me your worksheet, let me look at it and then get back to you?  send to fran@freedupdataoptics.com  

There are a couple of ways to achieve...