
1. Re: Calculating cumulative & consecutive years donations
Hari Ankem Jun 5, 2017 3:17 PM (in response to Amy Carrier)Amy,
This one is really challenging. But, I did get the output but for the fact that I did not consider the consecutive portion of whether the customer paid in FY16 or 17. This may need to be handled separately.
Hope this helps.

ConsecutiveGiving.twbx 41.1 KB


2. Re: Calculating cumulative & consecutive years donations
Amy Carrier Jun 5, 2017 3:39 PM (in response to Hari Ankem)This is wonderful and SO helpful. Thanks so much for working on this  I really appreciate it!

3. Re: Calculating cumulative & consecutive years donations
Hari Ankem Jun 5, 2017 3:41 PM (in response to Amy Carrier)You are welcome Amy.

4. Re: Calculating cumulative & consecutive years donations
Okechukwu Ossai Jun 5, 2017 6:40 PM (in response to Amy Carrier)Hi Amy,
Hari Ankem has correctly answered your question.
However, the beauty of Tableau is that you can achieve the same result using different methods. So, I've attached an alternative approach primarily for those who will be reading this thread in the future and may require a different approach due to the peculiarities of their datasets.
An alternative approach will be to use Lookup function. From your expected results, I assumed that you want to include incomplete or current fiscal years like 2017 in your consecutive count provided a donation was made the previous fiscal year. I have broken down the solution into several parts for easy comprehension.
Step 1: Create calculated field [Difference in Years]
LOOKUP(MIN([FY]), 1)  LOOKUP(MIN([FY]), 0)
This formula calculates the difference between a fiscal year and the next fiscal year within the partition.
Since this is a table calculation, you will need to tell Tableau how to compute the calculated field. So, put this in the view, right click and select Compute Using FY.
Step 2: Create calculated field [Consecutive Years]
IF [Difference in Years] > 1 THEN 0
ELSEIF LOOKUP([Difference in Years], 1) = 1 AND(ISNULL([Difference in Years])
OR LOOKUP([Difference in Years], 0) < 0) THEN 1
ELSE [Difference in Years] END
Using your requirement, The formula above includes the latest fiscal year in each partition and excludes records where difference in year is greater than 1.
Put this in the view, right click and select Compute Using FY.
Step 3: Create calculated field [Consecutive]
WINDOW_MAX(RUNNING_SUM([Consecutive Years]))
This sums up the consecutive years and returns the window maximum. Put this in the view, right click and select Compute Using FY.
Step 4: Create calculated field [Cumulative]
{FIXED [ID]: COUNTD([FY])}
This formula returns the distinct count of fiscal year for each donor ID.
Step 5: Create calculated field [Total Gift Amount]
{FIXED [ID]: SUM([Gift Amount])}
This formula returns total donation for each donor ID.
Step 6: Create calculated field [Row Filter]
FIRST() == 0
Put [Row Filter] on the filter shelf select True. Then right click and select Compute Using FY, then select True again.
This will filter out all rows except the first row in each donor ID partition.
Hope this helps.
Ossai

ConsecutiveGiving_OO.twbx 52.4 KB


5. Re: Calculating cumulative & consecutive years donations
Amy Carrier Jun 6, 2017 3:53 AM (in response to Okechukwu Ossai)Thank you so much, Ossai! It's great to learn different ways of approaching a solution. I appreciate your adding to my own "body of knowledge".
One complication is that in my greater data set, I don't actually have the FY field  I had created that for this example to show which FY each donation falls into. However, I'm sure there's a fairly simple calculation to create an FY data point that can be used in these calculations.
Thanks again!!
amy

6. Re: Calculating cumulative & consecutive years donations
Okechukwu Ossai Jun 6, 2017 4:24 AM (in response to Amy Carrier)1 of 1 people found this helpfulYou can easily do this with a calculated field. Let's look at the different options below which calculates FY for the period July 01 to June 30.
1. You can use the formula below which returns FY in "yyyy" Year format.
IF MONTH([Gift Date]) <= 6 THEN YEAR([Gift Date])
ELSE YEAR([Gift Date]) + 1
END
This result will return FY as measures . Since you need this field in your LOD expression, it'll have to be a dimension. So after the field has been created, right click on the field under the Measures pane and select "Convert to Dimension".
2. However, it seems you prefer FY in the Year format "yy". The formula below will do that for you.
INT(RIGHT(STR(IF MONTH([Gift Date]) <= 6 THEN YEAR([Gift Date])
ELSE YEAR([Gift Date]) + 1
END), 2))
Similar to what you did above, right click on the field name in the Measures pane and select "Convert to Dimension"
Hope this helps.
Ossai

7. Re: Calculating cumulative & consecutive years donations
Amy Carrier Jun 6, 2017 7:08 AM (in response to Okechukwu Ossai)That did it! Thanks again, Ossai.
amy

8. Re: Calculating cumulative & consecutive years donations
Okechukwu Ossai Jun 6, 2017 9:09 AM (in response to Amy Carrier)You're welcome Amy. I'm glad it helped.
Ossai