Thanks Ey. Not quite what I'm looking for, need the amount to sum accross the common id and fiscal year.
We use this formula to create the size of gift field.
IF [NGC_IN_$MM] >= 25000000 THEN "$25MM or More"
ELSEIF [NGC_IN_$MM] >= 10000000 THEN "$10MM to $25MM"
Elseif [NGC_IN_$MM] >= 5000000 then "$5MM to $10MM"
ELSEIF [NGC_IN_$MM] >= 3000000 then "$3MM to $5MM"
ELSEIF [NGC_IN_$MM] >= 1000000 then "$1MM to $3MM"
ELSEIF [NGC_IN_$MM] >= 500000 THEN "$500K to $1MM"
ELSEIF [NGC_IN_$MM] >= 250000 THEN "$250K to $500K"
ELSEIF [NGC_IN_$MM] >= 100000 Then "$100K to $250K"
ELSEIF [NGC_IN_$MM] >= 25000 THEN "$25K to $100K"
ELSE "Gifts Under $25K"
Recently we pre aggragated the totals on the SQL side but lost any dynamic filtering. We want to use a window sum function but when we do the size of gift becomes a measure and we need it to remain a dimension. Any ideas.
1 of 1 people found this helpful
I'm not clear on what your desired aggregations are. I'm guessing that each Common Id represents a donor, and you want to get the total number of distinct donors for each year and total amount from those donors within each Size of Gift bucket?
Also, you write that "We want to use a window sum function but when we do the size of gift becomes a measure and we need it to remain a dimension." but you don't describe what you want to do with that, except perhaps in the spreadsheet. With Tableau, it's helpful to be specific about your final goal because there are often several routes to get there, each with their own tradeoffs.
Based on what I was able to interpret, I set something up in the attached. The "special sauce" here is that for the discrete aggregate Size of Gift, I unchecked "Ignore in Table Calculations" in the view. This allows the calc to be used for partitioning and thereby be calculated over for the WINDOW_SUM calcs that generate the # of Donors and Total Amount.
Given the following layout of pills in the view, the two table calcs should have their Compute Using set to Common Id.
Also, make sure that the formulas for # of Donors and Total Amount are the same as what I'd sent in the previous post.
Let me know if this works for you, if not the next step would be to (possibly helpful) include a screenshot of the view that shows all the pills similar to what I sent, along with the Compute Usings for the table calcs, or (most helpful) a packaged workbook.
Jonathan, this works exactly how I want. Can't thank you enough!!
Last thing I need to figure out is how to improve the performance. When I have the common id on the level detail slows the dashboard down to a crawl, of course I have over 100K id's in the data set.
The IF and IIF statements in the calcs I provided are there for optimization, one additional optimization you can do is Ctrl+drag the Total Amount or # of Donors calc to the Filters Shelf, then filter for non-Null values. That will at least reduce the marks that Tableau is trying to draw.
The fundamental performance issue with table calcs is that Tableau needs all the data at the given level of detail, so for this view Tableau is pulling # of IDs * # of years rows of results from database at a minimum, so for 100K+ ID's * 5 years that's over 500K rows. That can take awhile to move the quantity of data out of the database and across the wire to your desktop. Extracts can speed this up, but not necessarily as much as needed, you can also aggregate within extracts so aggregating to the year level might be worthwhile. I don't know whether you could aggregate out Common ID and just have the Size of Gift come across in the extract, I haven't tried to go that far with extract optimization.
It helps me to think of calculations in Tableau as happening at 3 levels - data row level, aggregation, and table calculation, with N levels of nested table calculations possible. Maximal performance comes when Tableau has to do the minimum calculations possible. So, one thought would be to move the Size of Gift aggregate calculation into your data source (or just aggregate by year & common ID), then the necessary calcs for # of Donors and Total Amount could be regular aggregates and they'd definitely be fast enough.