I have successfully created a Pivot table showing all donors who gave an accrued amount of $1,000 and up to our Annual Campaign see image below. I can't however figure out how to make that into a 123 Indicator. I have copied the Pivot and changed it to the 123 Indicator and the values, filters and formulas all seemed to convert over correctly and although the Total Amount filter seems to allow me to add the Greater than or Equal to $1,000 condition it does not save it on close.
The Total Amount value is a favorite value I created in the Pivot which includes the formula >=$1,000 filter but when it is moved to the 123 Indicator it loses that condition. I'm guessing I need a formula in the Value of the Indicator rather than the widget Filter but I don't know how to write that formula. Any help would be greatly appreciated.
Hi Terry,
This is an interesting example. You aren't wanting to filter by distinct contribution Amounts, but rather by a set of Constituents: those that have an accrued contribution Amount of >= 1,000 in the selected dashboard campaign filter. Filtering on Amount by itself will limit your results to contributions of >= 1,000 rather than include constituents who might have 2 contributions totaling >= 1,000.
In my example, for the selected campaign, the total Amount in my row-per-constituent pivot table is 905,753. When I duplicate and filter that pivot table by rows where Amount >= 1,000, the total changes to 899,400. This is our correct number that we want to see in an indicator.
The third widget from the left is a duplicate of the second, however, with a filter in the widget updated from Amount > 1,000, to a filter on Constituent ID. Using the Ranking type filter, it's set to the Top 10,000 based on a formula.
CASE WHEN [Total Amount] >= 1000 THEN [Total Amount] ELSE NULL END
=>
The key here is in the use of NULL as the ELSE condition when a constituent's total giving within the selected dashboard campaign filter is < 1,000. Setting it to something else, let's say $0, will still include those constituents if there are fewer than 10,000 constituents with >= 1,000. It'll just pick those with >= 1,000 first, and then start including constituents with < 1,000 as 0s. Setting it to NULL however will exclude those constituents. And in that third/middle widget, we can see that the total Amount remains 899,400.
To prove that this filter would survive being rolled up to a higher level field, the fourth Campaign widget is a duplicate of the third widget, with Constituent ID removed and Campaign added in its place. The number was unchanged, so duplicating again, the fifth widget, an indicator shows the total giving Amount only for constituents who've given >= 1,000 within the selected dashboard campaign filter.
1000GiverFilter.dash
Best,Chris
Chris Wallingford Director, Business Intelligence Tessitura Network office: +1 888.643.5778 x553 chris.wallingford@tessituranetwork.com
Chris, This is supper helpful. Thanks.
ie: A tasty little dashboard