Hi,
Please refer to the attached workbook and Excel data.
Each COID has several PIDs. I have create a calculated field at the PID level which does the following.
if count([EVER])=countd([ID]) and sum([INQ])=0 then countd([PID]) else 0 end
So for each PID we have a value of 0 or 1 for the calculation. Out of the 162 rows you see in "sheet-1" 155 of them have a value of 1 and the rest have a 0 for the calculation.
I want to do this aggregation at the COID level, but the calculation should be done at the PID level
For example the second sheet should look like this
COID Calculation(1)
1280 155
I have tried a few things, but it seems like i am missing something. Please advise
Thank you
Hello,
This is an excellent example of where table calculations are useful to create a higher level of aggregation. I created the following calculated field:
IF FIRST() == 0 THEN
WINDOW_SUM([calculation], 0, IIF(FIRST()==0,LAST(),0))
END
The IF FIRST() and IIF() statements are to reduce the number of results returned so we don't get overlapping text, this uses a technique developed by Richard Leeke here: http://www.clearlyandsimply.com/clearly_and_simply/2011/01/another-look-at-site-catchment-analysis-with-tableau-6-part-3.html
The meat of the statement is the WINDOW_SUM([calculation]…). Drag that into the view, set the Compute Using to PID (assuming both COID and PID are available in the view), and you're all set. This is done in the attached workbook.
Cheers,
Jonathan
