I have an excel spreadsheet that I want to convert to an analytics dashboard. The spreadsheet shows # of gifts, # of HH and total gift amount for the last 10 years of giving broken down by giving level and year. My issues is with the custom ranges in Analytics. I've created a custom range division with our giving levels and I created a customer range which works fine if I only want to see the gifts for this fiscal year. If I attempt to use the same custom range in a widget and look at giving for the last ten years I get total giving over the last 10 years for the donors instead of giving per year. This is what I'm using in the table:
Data Select: sum(cont_amt)
Data From: dbo.V_CUSTOMER_WITH_PRIMARY_AFFILIATES q join dbo.VS_CONTRIBUTION c on c.customer_no=q.expanded_customer_no join dbo.VS_CAMPAIGN s on c.campaign_no=s.campaign_no
Data Where: s.fyear = (SELECT MAX(fyear) FROM dbo.TR_Batch_Period WHERE GETDATE() BETWEEN start_dt AND end_dt)
I've tried it with just cont_amt and it caused the load to fail. I've also tried dropping the where clause and I still just get total giving for the last 10 years.
Anyone have any ideas how I can get this to display just giving for the specific fiscal year?
PS - I can't use the membership cube because we've changed membership levels so many times in the last 10 years that it's a mess.
Thanks!
Did you ever find a solution for this? I have a similar use case.