7 Replies Latest reply on Nov 15, 2017 6:55 AM by John Rivett

# Distinct Running Count of First Gift (Version 10.1)

I need to pull the earliest gift date for each fiscal year (which starts Oct. 1)  per constituent record and then do a distinct running count of the earliest gift dates.

My uploaded Excel sample has three people, Mark, Michelle and Louis, with multiple gifts and I’m having a hard time getting this to work with LOD and min calcs and pinpointing just the one gift. For instance in the highlighted rows in the Excel attachment, Michelle has given her first gift on 10/1/2017 (in fiscal year 2018) followed by three others. I only want her 10/1/2017 gift to show in the running count in a visualization broken out by fiscal year month; the other two I don’t want in the visualization. For FY2017 Michelle’s first gift would be on 11/29/2016 (she has two gifts on this same date but I only want a distinct count of the first gift of each fiscal year). Another example, Louis has two gifts on 12/1/2015 but I only want to count it once for FY16. Mark, his first fiscal 2017 gift would be on 10/6/2016.

Can someone help me with this? My other workaround in the custom SQL was using the min function for the date and grouping by fiscal year, but I want to know how to accomplish this in Tableau when I have multiple records per donor for a given year.

• ###### 1. Re: Distinct Running Count of First Gift (Version 10.1)

Hi John,

I can't open your workbook, but let me take a stab at this.

if [Date_of_record] = {FIXED [First Name], year([Date_of_record]) : MIN([Date_of_Record])}

and [GP_KEY] =  {FIXED [First Name], year([Date_of_record]) : MIN([GP_KEY])}

then [Date_of_record] END

To return one row, you'll need to consider the GP_KEY as well. This should yield only the record with the MIN GP_KEY and the MIN Date for each year.

Next, set your fiscal year start appropriately, as illustrated here:

1. Right-click (Control-click on Mac) the data source in the Data pane to open the Date Properties dialog box.
2. Set the Fiscal year start field to the appropriate month.

Edit: I realize that Fiscal Year can't be called in calculations. Substitute this in place of year([Date_of_record])

DATEPART('year',DATEADD('month',1-FiscalYearStartMonth,[[Date_of_Record]])) where FiscalYearStartMonth = integer month of fiscal year start month.

if [Date_of_record] = {FIXED [First Name], DATEPART('year',DATEADD('month',1-FiscalYearStartMonth,[[Date_of_Record]])) : MIN([Date_of_Record])}

and [GP_KEY] =  {FIXED [First Name], DATEPART('year',DATEADD('month',1-FiscalYearStartMonth,[[Date_of_Record]])) : MIN([GP_KEY])}

then [Date_of_record] END

• ###### 2. Re: Distinct Running Count of First Gift (Version 10.1)

May be you need this?

Here is view:

• ###### 3. Re: Distinct Running Count of First Gift (Version 10.1)

Make these changes and see if it works for you:

You can now do a running count as required. Hope this helps.

• ###### 4. Re: Distinct Running Count of First Gift (Version 10.1)

Thanks so much Galen!! I really appreciate your help.

• ###### 5. Re: Distinct Running Count of First Gift (Version 10.1)

Thanks so much Hari. I really appreciate your help.

• ###### 6. Re: Distinct Running Count of First Gift (Version 10.1)

You are welcome.

• ###### 7. Re: Distinct Running Count of First Gift (Version 10.1)

Thanks Deepak. You must have an older version as I can’t open the workbook. Would you mind sharing what you did with calculation 1?