We're looking to reverse engineer a Finance report to look at Contributions in Analytics. We have a report that groups contributions by Income Designation. We're looking at creating a YTD budget, and an annual budget for each Income Designation. Hard-coding a set number as a value would have that value be the same across all rows, and we want each row to be different- each row would be its own Income Designation, and each Income Designation would have it's own YTD budget (changes as the year goes on) and annual budget (static through the year). Is this possible?
I tried making a nested series of 'IF/THEN' statements, using IF ( [Contribution Amount] , [Income Designation = X] > 0, use this [budget amount] , otherwise (go down the list and create an if/then for each designation)....
However, when I do this, it just stops after the first statement, because there are in fact contributions in that first Income Designation. I feel stuck.
I could also pull the report into Excel and manually update certain columns, but I want to automate whenever possible.
Hi Nathanael,
See if this post re CASE/WHEN helps?
Unfortunately, there doesn't seem to be a number I can latch onto for each Income Designation. There's no 'Income Designation ID' to work with- just the 'Income Designation' itself, which led me to try the (Amount , Designation = X) formula.
Yeah, you'll need to try the logic you were using in your IF, but with a CASE.
CASE WHEN ( [Total Amount] , [Income Designation = X] ) > 0 THEN 10000WHEN ( [Total Amount] , [Income Designation = Y] ) > 0 THEN 12000...ELSE SUM(0) END
If this is going to be used in combination with [Income Designation] though, using "> 0" won't give you your expected result. The value filter will override the Row grouping, and regardless of which [Income Designation] Row, the CASE statement will return the budget amount associated with the first Income Designation that has a [Total Amount] > 0.
Instead, we'd need to see if this row is Income Designation = X, and if so, return the budget amount for X. To do that, instead of comparing to 0, compare to the unfiltered [Total Amount] for the row...
CASE WHEN ( [Total Amount] , [Income Designation = X] ) = [Total Amount] THEN 10000WHEN ( [Total Amount] , [Income Designation = Y] ) = [Total Amount] THEN 12000...ELSE SUM(0) END
There's still a chance of getting the wrong budget when more than one Income Designation has the exact same [Total Amount], so you'll need to weigh the likelihood of that. Were that to happen in the above example, X and Y would both show 10000 as their budget, because that first WHEN would be true for both Income Designations, and CASE returns the THEN from the first true WHEN.
Progress! Thanks again.
Follow up question: When my formula is this:
CASE
WHEN ( [Total Amount] ,[Capital] ) = [Total Amount] THEN 250000WHEN ( [Total Amount] ,[add-on donation] ) = [Total Amount] THEN 2WHEN ( [Total Amount] ,[board unrestricted] ) = [Total Amount] THEN 25000WHEN ( [Total Amount] ,[corporate] ) = [Total Amount] THEN 40000WHEN ( [Total Amount] ,[development] ) = [Total Amount] THEN 5WHEN ( [Total Amount] ,[in kind] ) = [Total Amount] THEN 0WHEN ( [Total Amount] ,[preservation fund] ) = [Total Amount] THEN 7WHEN ( [Total Amount] ,[restricted] ) = [Total Amount] THEN 20000WHEN ( [Total Amount] ,[special events] ) = [Total Amount] THEN 800000WHEN ( [Total Amount] ,[unrestricted] ) = [Total Amount] THEN 559000
ELSE SUM(0) END
that last row returns as 0:
When I change that 'ELSE SUM(0) END' to 'ELSE SUM(X), I get wildly different numbers ( 1 -> 17, 559000 ->9503000).
Why is 'unrestricted' acting so strangely?
I'm not following exactly, but you may be running into some row inflation. Try "0" instead of "SUM(0)". Then, if still wonky, surround the whole CASE in a MAX(). e.g. MAX ( CASE ... END ).
Also, the value will need to be set with Subtotal as Sum instead of Auto.
I tried the 0 instead of sum(0), and the CASE( everything), and neither returned anything besides a 0 for 'unrestricted'. I've also set the 'subtotal by' to sum, which does return a number for the grand total, rather than 0.
Not super helpful to hear, I'm sure, but maybe encouraging that we're on the right track... a quick test here looked good:
Try pulling out ( [Total Amount] , [unrestricted] ), double checking that filter, and [Total Amount] into their own values and see that they're returning the same number on the unrestricted row.
I created a formula for ( [Total Amount], [ unrestricted] ) , and it returned exactly the amount that's in the 'this year' column, in the 'unrestricted' designation row. So Tessitura agrees with itself on what ( [Total Amount], [ unrestricted] ) means.
When I plug that back into the 'Case/When' formula, it still returns as '0'. When it's the only 'When' that exists, it's a 0, and when it's part of the other group of 'Whens', it's a 0. I deleted it and re-added it, to no avail.
By the logic of the formula, for some reason ( [Total Amount], [ unrestricted] ) =|= Total Amount, for that row, right?
Would you push this into a Support Ticket please, Nathanael? We need to see more first-hand what's happening.
Thanks
Done!