1 Reply Latest reply on May 22, 2012 11:40 PM by Jonathan Drummey

# Row level calculation and high level aggregate -

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

• ###### 1. Re: Row level calculation and high level aggregate -

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