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
Hi Chris, thank you so much for jumping in on this. Unfortunately the numbers I am getting with your dashboard are not matching the number of constituents we are expecting. Constituent IDs are missing for those who gave less than 1000 in a single transactions but their accumulated transactions for the Annual campaign are 1000 or more. I get 158 using your dash and we are expecting 164. Our total amount is also not the same, yours is much higher than what I expect.
I'm going to start a ticket on this.
Here is your dash with my widget added. 1000GiverFilterCHRIS Dash with TERRYS Widget.dash
What I had to do to get the accumulated total for 1000 and up was to add the Amount value in the PIvot. It originally showed as SUM [Amount] but I needed Total Amount. So, I then right clicked the [Amount] selected Sum on that, it then showed as SUM [Total Amount] and I removed the SUM in front of it. Then I added the filter of >= 1000 and seem to get the correct number of constituents. I tried several different things and only this seem to work. I knew which IDs we needed who gave those accumulated amounts and they then showed up. I also checked several of the amounts and believe the amount is also correct.
I just can't seem to change this into a 123 indicator. All formulas are lost. The indicator values work a bit differently and I can't see how to add a filter to the value.