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
RAMP customers probably won't be able to do this, but I've added a custom column to the CONTRIBUTION DATE table of the Contributions cube in our DEV environment which serves as a flag for whether the date counts for Fiscal Year-To-Date. The code for the new column is:
AddYears(CurrentDate(),[Fiscal Current Year Offset])>=[Date]
As you can see, it returns true if the current date adjusted by the Fiscal Current Year Offset is greater than or equal to the date being checked. Now if I want YTD values, I just filter by whether that column is true. Initial testing looks good, it's very easy to use, and it would also be really easy to roll something like this out in an upcoming release.