Hi All!
I'm still fairly new to Anayltics and am hoping someone can offer a solution to an issue I'm facing. I am trying to pull together reports showing number of gifts in each of the last five fiscal years broken out into four different amount sections:
$250 and below
$250-$1,000
$1,000-$5,000
$1,000+
I was able to create separate ones for each amount section in each FY (so showing number of gifts $250 and below in FY18 and another for gifts $250 and below in FY19) as Indicator widgets, but we really want some pretty graphs as well. When I try to make a Column Chart, though, I can't figure out how to consolidate those amounts into the ones I want, even filtered by a single FY. I've got the Total Contribution Count set as my Values and Amount set as my Category, with a Value Filter of Between .01 (to catch those $0 from adjustments) and $250, but it just breaks it down by individual amounts (i.e. 80 gifts of $10, 2 gifts of $23, etc.) instead of showing "[#] gifts of $250 or under. It's possible that what I'm asking for isn't doable in Analytics, but wanted to put out feelers in case there was something I was missing.
Thanks!Colleen
Sorry I can't be more specific - for some reason the Analytics cubes are still not updating in my organization. (Help me Tess!)But - You want to make a conditional field that categorizes the amounts for you, rather than using a field with all amounts included. I recommend using an IF/THEN statement. I asked at the Tess conference, and I was told you can place an IF/THEN statement into the field box directly where you would enter any SUM or MAX formula. Your statement could look something like IF Amount<=250, THEN "$250 or Less" ELSEIF Amount<=1000 THEN "$251-1000" ELSEIF Amount<=5000 THEN "$1000-5000" ELSEIF Amount>5000 THEN "$5000+" END.
This blogpost is for Tableau, but gives you the gist. https://interworks.com/blog/ccapitula/2015/04/01/tableau-essentials-calculated-fields-logical-functions/
Hi Colleen,
In your column chart, used filtered values (here's the help link). Create a value for Total Contribution Count that's filtered by Amount between .01 and 250.
In a new widget, select Total Contribution Count. Follow it with a comma and surround it with parenthesis. Then search for Amount and select to add it as a Filter from the More... menu.
Use the Values type filter and with the "between" operator selected, set your min/max.
Give the value a friendly name, and then duplicate it.
Give the new value a friendly name, and edit the filter for that value.
==>
I should mention that the conditional statements in Analytics (IF and CASE) are strictly limited to looking at the aggregated values that result and not the raw data. You cannot say "IF a given contribution has an amount <$250, then count it otherwise don't." Instead it's more like, "IF the sum of all the contributions in (Total Contribution Amount) is less than $250, show <this formulated or hardcoded value> else show <this other formulated or hardcorded value>.
More information on these formulas can be found in the function list, and hovering over the function to expose these tooltips:
Best,Chris
Chris Wallingford Director, Business Intelligence Tessitura Network office: +1 888.643.5778 x553 chris.wallingford@tessituranetwork.com
Is that on the road map to be addressed? That is a pretty big limitation to the customization of Analytics. Also can someone update the Houston Ballet on our ticket?
Thank you! I just did it and it worked perfectly!
Hi Angela,
Between Value filters, which do have access to the raw data for filtering, and Conditional functions at the aggregate level, we’ve found that use cases are being met. If you have requirements regarding specific use cases, I’d be interested in hearing about them. You can email (below) them to me directly.
Many thanks!Chris