Hi all
It might be easier to explain this on a call but I thought I'd give it a go here first....
We currently have some of our donations sitting 'In Fund' (providing us with a positive number) and some sitting 'On Account' (providing us with negative number).So I have 2 widgets which displays the following - this is all fine, but the merging to create one 'donation pot' it doesn't play ball!- Total Contributions 'In Fund' (from 2 funds)- Total Transactions 'On Account' (from 2 On Accounts)
In analytics, how can I get these to create one pool, so I can showcase the total 'donations pot'
I have been playing with the following filters - which isnt working so far!- Season- Fund- Payment Method& have made my negative n account pot positive by using *-1
Thanks in advance
Celia
Also - does 'Sum(Total Transaction Amount)' give all transactions - rather that what s currently sitting in On Account?
Sum(Total Transaction Amount) will give all transactions unless filtered down!This is a tricky one, because you don't want to filter the fund amounts with the on account filters and vice versa. I think you could accomplish this with filtered values, though. I've linked to the documentation on how filtered values work. I think that something like (sum([Contribution Amount]), [Campaign Fiscal Year], [Fund]) + (-1*(sum([Transaction Amount]), [Payment Method Type], [Years in Date])) (where your Date is the Transaction Date) would do it. That's a pretty off the cuff formula, but hopefully combined with the documentation, it'll get you closer to where you want to be!
Assuming all of your data is all from the same Cube, I agree with Mary's approach.
1. Duplicate one of your widgets (Fund).
2. Use the Filted Value concept to get your Fund Contribution value to match your original Fund widget amount.
3. Duplicate your other widget and use Filtered Values to get it to match.
4. Add the two formulas together in one widget.
Unfortunately, if the data is from two different Cubes, I know of no way to do this.
Thanks Neil!