Hi everybody,
I've built an analytics dashboard that shows me total contribution amount, contribution count, and average contribution amount by appeal category. I've got a Current Fiscal Year Offset filter with -5, through 0 selected, so I can see how the data changes year over year, but I'd like to use the date and time functions to be able to have a year to date comparison. So far, I'm unable to get any of these functions to work.
I'm trying to use the YTDSum([Amount]) where Amount is contributions amount. The formula description is: Returns the running total starting from the beginning of the year up to the current time period member. The Time dimension to be used is determined by the time resolution in the widget/dashboard.
I've tried adding various filters on both the widget and dashboard etc that refer to the fiscal year, as that's how I'd like to measure YTD, but always get the same syntax error: Expecting parameter of type 'Measure' but found 'Set'.
Can anyone clarify for me the distinction between measure and set in this context? Has anyone successfully gotten these date and time functions to work?
Thanks for any and all advice!
Sarah Marrs
Senior Manager of Individual Giving
Children's Theatre Company
smarrs@childrenstheatre.org
HI Sarah,
I appreciate your struggles around this and we'll definitely see some improved (simplified) methods for getting at these values in a future release. For the time being, the native functions for values like YTDSum do not support fiscal years, only calendar years. Rather than dig into those native functions here, it sounds like you're after a contribution amount for this fiscal year, compared to past fiscal years up to the same point in those fiscal year as we are currently.
This is a link to a sample dashboard. Save the file locally if you're accessing Analytics in a browser. If you're accessing it in the Tessitura application, then save it to a file location you can access from with RAMP. Then in Analytics, from the same Options menu where you select New Dashboard, select Import Dashboard, and then browse to this file. You'll get a success message for the import, and then the dashboard will throw a datasource error. To point it at your Contributions cube, click the "Contributions" datasource at the top, select Change Data Source, and then select MINN-Contributions. From that point it should populate the dashboard.
I had to use a combination of Fiscal Current Year Offset and Date filtering, on the values themselves, to limit each fiscal year's contribution amount to days in those fiscal years prior to today in those years. The pivot table has values for the current fiscal year and the prior 5 years. I've also included an indicator widget with an At-a-Glance Contributions style to it. showing the variance between the current and prior fiscal year to date, and a secondary value that is the current fiscal year total.
Best,Chris
Chris Wallingford Director, Business Intelligence Tessitura Network Office +1 888.643.5778 ext 553 chris.wallingford@tessituranetwork.com Tessitura Learning & Community Conference July 28-August 1, 2019 #TLCC2019
Chris Wallingford This is very cool thanks! This is working very nicely in my analytic environment as well.