• Replies 2 replies
  • Subscribers 962 subscribers
  • Views 307 views
  • Users 0 members are here
Related

Graphing YOY Sum Contributions, stacked Fiscal Years

Natalie Groom
$organization

Hello! 

I'm new to my organization and new to Analytics. I am trying to graph Year-Over-Year cumulative contributions with a window of September-August (our fiscal year, so not by calendar year). I've spent hours noodling and can't figure it out! Any ideas would be welcome.

In this graph I was able to see all FY cumulative contributions side-by-side but not stacked:

In this graph I can achieve a stacked line graph, but the months are in alphabetical order.

Then I can get a bar chart that has correct months but must not be showing cumulative contributions, otherwise the colors would all be trending upwards towards the close of our fiscal year

  • $organization

    Hi Natalie,

    For the month sort try adding Calendar Month Sort over the Month to organise

    However this might be worth a look (it's something I whipped up for another org)

    • X axis is days since campaign start 
    • Values are RSUM([Total Recieved Amount])
    • Break by is Campaign Fiscal Year
    • And filter is Campaign

    Let me know if you want the .dash file to look at

  • $organization

    Hi Natalie,

    Adding on to Heath, I recommend either the Column chart you have, but with an updated value to give you that running sum.

    Or from your first attempt where the FY cumuliatives were broken by fiscal year, but not stacked:

    • remove the break by field 
    • remove the filter on Fiscal Year
    • enable the Months in Date filter and set it to include only the months of the last fiscal year (Last and not current so that we can include all months as I'm assuming that if you try to filter on all months in the 2022 FY, many will not be available for selection because they don't exist in the data yet.)
    • rename the RSUM(Amount) value to 2021
    • from the options menu of the 2021 value, select Duplicate
    • from the options menu of the new 2021 (1) value, select the Past Year quick function
    • This will change the value to report on the same [Total Amount] value, but from one year prior to the month in your widget. This will also remove the RSUM() aspect of the formula. Edit the formula and updated it to 
    RSUM( PASTYEAR( [Total Amount] ) )
    • Click OK and rename the value to 2020.
    • To create values for years prior to 2020 (and for 2022), we'll need a different function to report on "Previous time periods", listed in the Functions list as PREV.
    • To create a formula for 2019, still surrounding the whole formula with an RSUM(), surround the inner formula with () as well to treat [Total Amount] as a filtered value, and follow it with the PREV function. Since we're using Months on the chart, we put the [Months in Date] for the contribution date and the count of months back to look.
    RSUM( ( [Total Amount] , PREV( [Months in Date] , 24 ) ) )
    • OK and rename that formula 2019.
    • Duplicate and edit to 36 months for 2018.
    • For 2022, duplicate, edit and either change it to -12 months, or switch from the PREV to the NEXT function and a positive 12 months. 

    For consistency with this approach, when you know you're going to be doing more than one FY, the PASTYEAR function for 2020 can be replace with PREV and 12 months.

    I'm attaching my dashboard from the screenshot above for your reference (Importing a Dashboard). 

    RunningSumYoFY.dash

    You can change the display of the display of the Months from showing the Year to only showing the month name using this "Format Date" icon:

    Best,
    Chris