8 Replies Latest reply on Jun 6, 2017 9:09 AM by Okechukwu Ossai

    Calculating cumulative & consecutive years donations

    Amy Carrier

      Hello, Tableau community experts!

       

      I need some help calculating cumulative and consecutive years of donations by individuals.  I have attached a workbook with a few data points.

       

      The first challenge is that our fiscal year runs July 1-June 30.  I have changed the default properties of the Gift Date, but am not sure if there's another place it would need to be changed.

       

      For each of the three donors listed, I need to know how many fiscal years they have given cumulatively and how many consecutively.  "Consecutive" is only valid if they have given in the most recent completed fiscal year (which, in some situations, would be FY16, as FY 17 isn't yet over).

       

      Based on this, the results should be as follows:

       

      For ID 328501, they have given 5 years consecutive and 5 years cumulative.

       

      For ID 729224, they have given 3 years consecutive and 4 years cumulative.

       

      For ID 892105, they have not given consecutively (they did not make a gift in FY17 or FY16), but have given 3 years cumulative.

       

      Any help with how to calculate these would be greatly appreciated!

        • 1. Re: Calculating cumulative & consecutive years donations
          Hari Ankem

          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.

           

          Capture.PNG

           

          Hope this helps.

          1 of 1 people found this helpful
          • 2. Re: Calculating cumulative & consecutive years donations
            Amy Carrier

            This is wonderful and SO helpful.  Thanks so much for working on this - I really appreciate it!

            • 4. Re: Calculating cumulative & consecutive years donations
              Okechukwu Ossai

              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

               

               

               

               

              1 of 1 people found this helpful
              • 5. Re: Calculating cumulative & consecutive years donations
                Amy Carrier

                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

                  You 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

                   

                  1 of 1 people found this helpful
                  • 7. Re: Calculating cumulative & consecutive years donations
                    Amy Carrier

                    That did it!  Thanks again, Ossai.

                     

                    amy

                    • 8. Re: Calculating cumulative & consecutive years donations
                      Okechukwu Ossai

                      You're welcome Amy. I'm glad it helped.

                       

                      Ossai