0 Replies Latest reply on Jan 31, 2013 2:14 PM by Stephen Gordon

    How to get distinct counts of donors based on lapsed or current?

    Stephen Gordon

      I have imported a table with a set of transactions from our database into Tableau. I am trying to make a worksheet that will give me distinct counts of:

      1. Donors who have given sometime but haven't given in the last 36 months

      2. Donors who have given in last 36 months but haven't given in the last 24 months

      3. Donors who have given in last 24 months but haven't given in the last 12 month

      4. Donors who have given in the last 12 months

       

      Ideally I could produce a table like the following:

       


      Given in the last 12 months
      Given in last24 months but not in the last 12 monthsGiven in last 36 months but not in the last 24 monthsGiven sometime but not in the last 36 months
      Number of donors1502002050

       

      I used the calculation below to try to get a list of customers from this set of data who fall into the following categories:

       

      if datediff('month',WINDOW_MAX( max([Date])),today())>12 then "Lapsed - Hasn't Given in last 12 Months" elseif

      datediff('month',WINDOW_MAX( max([Date])),today())>24 then "Lapsed - Hasn't Given in last 24 Months" elseif

      datediff('month',WINDOW_MAX( max([Date])),today())>36 then "Lapsed - Hasn't Given in last 36 Months" else

      "Current" end

       

      Unfortunately it ended up looking like this:

      lapsed3.jpg

       

      Any ideas?

       

      Appreciate any help!!

      Thanks,

      Stephen