1 of 1 people found this helpful
Use WINDOW_COUNT to count aggregates.
Other aggs to agg aggregates:
Thanks for your reply Joe.
I tried using WINDOW_COUNT on the calculated column I used in step 2 above but it returns a flat value for every entry in the column, which is equivalent to a total count of the quantile to which each value belongs. So (in English) if there were four rows, the first two rows had Spend such that they belonged in Quantile 1, the third row had Spend such that it was assigned to Quantile 2, and the third row had Spend such that it belonged to Quantile 3, the WINDOW_COUNT returns 1 + 1 + 2 + 3 = 6 as the value for all 4 rows.
What I want to achieve is that each quantile has a COUNT of the number of rows that fit into that quantile, so really in this reduced example I want three rows, one column with the quantiles, one with the vlaues 2, 1, 1. Does that make sense?
WINDOW_(whatever) makes a table calc. You can specify how a table calc gets partitioned (by default tableau does either TABLE(doen) or TABLE(across) ) and sometimes you have to tell ti to do something different.
At this point I can only guess what you'll need to do to tell it where to run, and where to restart. I'll need an extracted workbook (TWBX) to play with.
Either you can clinck rhe advanced editor where you can do attachments, or edit one of your replies (which puts you into the advanced editor.)
on top right corner, use advanced editor.
On Wed, Feb 25, 2015 at 11:21 AM, Hamish Wilson <
Done - attachment above. Is that just the raw data in the .tde file? Or does that give you access to any analysis I've performed on the data as well?
If it doesn't give you the analysis then just a brief explanation of what I've done.
1. Placed Supplier on Rows
2. Placed Spend on Columns
3. Toggled to show text rather than default graph (very annoying - can I set text as the default somehow?)
4. Clicked Analysis > Create Calculated Field and created a Field called maxSpend with the formula TOTAL(MAX([Spend])).
5. Added maxSpend to Rows and again toggled to show text rather than graph.
6. Clicked Analysis > Create Calculated Field and created a Field called [quartiles] with the formula
IF (SUM([Spend]) < maxSpend*(1/4)) THEN 1
ELSEIF (SUM([Spend]) < maxSpend*(2/4)) THEN 2
ELSEIF (SUM([Spend]) < maxSpend*(3/4)) THEN 3
7. Added [quartiles] to Rows and again reverted to showing text rather than graph.
That's as far as I've got. Now what I want to do is essentially ignore the first three columns; Supplier, Spend and maxSpend, and count instances of [quartiles] as a frequency distribution; so 1 occurs 4 times, 2 occurs twice etc.
Hamish -- In Tableau Desktop under FILE select "Extract Packaged Workbook".
That gives you a TWBX containing your workbook and data. Upload that here.
Lookinfg at this further, what's wrong with the voz you provided? Aren't those quartile numbers correct? the MAX is correct. (WINDOW_MAX would have done that too.) And the various sums are getting proper quartile numbers, aren't they?
If not, what are you expecting?
Everything there is correct and CC's are functioning as intended, but there's another step I don't know how to realise - I now want to aggregate over the quartile column. So ultimately I want to end up with
Quartile Freq 1 4 2 2 3 2 4 2
but if I try to now COUNT(quartile) it complains that I'm aggregating over an aggregate column. You mentioned something earlier about window_summing with partitioning? That sounds similar to T-SQL window functions that would achieve what I'm looking for in that context
1 of 1 people found this helpful
Not as easy as I thought it would be.
I can get the quartiles to sort so all the 1s and 2s are together, but I can't get the next calc (window_count([quartile])) to restart for each quartile. (See Calculation1).
I even thought I could get a calc to conditionally count. (See calculation2). But all that's doing is acting on the table in the order shows on sheet1.
Jonathan Drummey -- is there a way to get this to happen?