Hello all,
This one is challenging me. I have created a campaign dashboard where I have all gifts YTD for this year and the two years previous. When I view this data as a bar chart, it is correct, and counts all of the gits. When I view it as a pivot, several of the gifts are missing.
Correct number:
Incorrect number:
I also noticed that If I remove the constituent IDs, I received the correct value for 22-23, but then 23-24 disappears.
My Values formula is
case when [Total Campaign Fiscal Current Year Offset] = 0 then sum([Amount])when [Total Campaign Fiscal Current Year Offset] = -1 then (sum([Amount]), [Days in Date])when [Total Campaign Fiscal Current Year Offset] = -2 then (sum([Amount]),[Days in Date1])end
and the date has a custom filter:
For campaign fiscal year offset -1, it is:
{ "last": { "count": 9999, "offset": 365 }, "custom": true}
For campaign fiscal year offset -2, it is:
{"last": {"count": 9999,"offset": 730},"custom": true}
Finally, here is the dashboard:
GalaTracking1.dash
I appreciate your guidance.
Sometimes with pivot tables adding in additional categories can under count (count unique) rather than aggregate. Do you know the Constituent IDs of the missing gifts? Or if you put the contribution ref_no in below the Constituent ID in case it's deduping a payment
For the missing 23-24 - I wonder if you still need the Days in Date in your formula, and the Campaign? I just recently put together a dashboard exactly like this, but I set up mine a little different.
For my Fiscal YTD Values for 23-34 in the widget, my formula is
(SUM([Amount]), [Campaign], [Days in Date])
And the filter in my Days in Date is:
{ "last": { "count": 10000, "offset": 0 }, "custom": true}
Everything I needed to know for these formulas I got from this video - www.tessituranetwork.com/.../Hands-on-YOY-Reporting
Hi John,
In your CASE statement, please try using MAX instead of SUM. Taking a sum will add together -1 + -1 for each contribution resulting in a value equal to -1 x [Total Contribution Count] instead of the simple -1 you're hoping for.
Also, Pivot tables require a field on Rows to render correctly. If you need something that will always result in just a single row, you can always use the CONSTITUENT, Division field.
Thank you, Chris!