1 Reply Latest reply on Jun 18, 2016 4:30 PM by Yuriy Fal

    Create List of Customers by Month That Didn't Have Sales in Previous Month

    Clark Stevens

      I have an interesting problem that I am having a tough time finding a solution to. Given a list of customers and their revenue, I want to generate a list of customers that didn't have revenue in the previous month but do now. The idea is to have a list, for each month, of customers who didn't have any revenue but is active now so the account management team can use this to target accounts that need attention. I will do the reverse as well, customers who had revenue last month but none this month. I need it for the last six months.

       

      Month -5
      Month -4Month -3Month -2Month -1Current Month

      Cust xyz

      Cust 123

      Cust sdg

      Cust 58q

      Cust 89g

      Cust ;ok

      Cust 546

      Cust hg4

      Cust 45k

      Cust iu9

       

      In the attached workbook I generated a crosstab that has the sum of sales and the sales last month. It also gives the customer name only if there was revenue this month and none last month. The behavior I want is to be able to take Customer Name off of Rows and everything off of text but the customer name so I am left with, for each month, just the name of customers that had sales in that month but not in the previous month. There is also a second tab called LOD where I fooled around with LOD's to get this behavior unsuccessfully.

      Two issues I run into:

      1) Table calcs cant be used within LOD's (cant do {FIXED Customer : Lookup(SUM(Sales),-1)}

      2) The INDEX requires the measure to be aggregated so when I take customer name off of columns it rolls everything up

       

      Any help would be appreciated