6 Replies Latest reply on Jun 19, 2018 7:50 AM by Darren Braynard

# Aggregation of aggregation question

Hi, very new user here...

I have a problem very similar to the one here: Re: Calculation to return the maximum value for each user?

Here is the concept. I start with this data (for simplicity there is only one model in this example). A model has many views and a view has many elements. Suppose a session is the result of analyzing the view and recording elements that had errors.

 Model View SessionId ElementId TotalElementsInView 1 Front 10 A 50 1 Front 10 B 50 1 Front 10 C 50 1 Back 11 D 40 1 Back 11 E 40 1 Back 11 F 40 1 Front 12 A 55 1 Front 12 B 55

I can create this next view using a calculated field and filter:

 Model View LastSessionId ElementErrorCount TotalElementsInView ErrorRate 1 Font 12 2 55 3.6% 1 Back 11 3 40 7.5%

I did this with the help of a calculated field similar to the one in the above linked thread, and I applied the filter with a the SessionId as the specific dimension:

```IF MAX([SessionId])=WINDOW_MAX(MAX([SessionId]))
THEN true
ELSE false
END
```

Now, I need help with creating the next view which is a summary of the previous sheet.

 Model ElementCount TotalElementsInModel ErrorRate 1 5 95 5.3%

I would like to avoid doing this entirely in MS SQL in views with sub-queries and instead use the power of tableau. I tried LOD calculations but could not figure out how to apply them in this case.

• ###### 1. Re: Aggregation of aggregation question

I'd suggest a series of calculated fields as follows:

Element Count

// Get Distinct Count of all Elements

COUNTD([Element Id])

Total Elements Per View

// Get the max number of elements in each view.

{FIXED [View]: MAX([Total Elements In View])}

Total Element Count

// Sum up the total elements per view.

SUM([Total Elements Per View])

Error Rate

// Calculate the error rate.

[Element Count]/[Total Element Count]

See the attached workbook.

1 of 1 people found this helpful
• ###### 2. Re: Aggregation of aggregation question

I appreciate the effort Ken, but I think that I'm looking for a slightly different answer. The "Element Count" (i.e. total number of elements with errors across the Model) should be 5, not 6. And therefore, the ErrorRate should be 5.3%.

• ###### 3. Re: Aggregation of aggregation question

Hi Check the below calculations :

Last Session Id : { FIXED [Model],[View] : MAX([Session Id])}

Error Element Count :

{ FIXED [Model],[View] : COUNTD(

(IF [Session Id]=[LS] THEN [Element Id] END))}

Total Elements : { FIXED [Model],[View] : AVG(IF [Session Id]=[LS] THEN [Total Elements In View] END) }

Error Rate :  (SUM([ErrorElementCount])/SUM([TotalElements]))*100

and the result are below :

attached the workbook for reference.

1 of 1 people found this helpful
• ###### 4. Re: Aggregation of aggregation question

Hi Darren,

Create few calculated fields using below mentioned formula

[Max Session]: {FIXED [Model],[View]:MAX([Session Id])}  // It will result last session id per view

[Elements]: COUNTD(IF [Session Id]=[Max Session] THEN [Element Id] END) THEN [Element Id] END) // It'll result distinct count of total element per view as per the last session

[TotalElementsView]: SUM({FIXED [Model],[View]:MAX(IF [Session Id]=[Max Session] THEN [Total Elements In View] END)}) //Formula is quite similar to above one only change in agg() and measure. It returns TotalElementView of latest session per view.

Error Rate %: [Elements]/[TotalElementsView]

Now drag the fields in canvas and see the output

Final View: Remove unwanted fields from the canvas

Let us know if this help. Workbook v10.5 attached for your reference.

Mahfooj

1 of 1 people found this helpful
• ###### 5. Re: Aggregation of aggregation question

This works, I appreciate the help!

• ###### 6. Re: Aggregation of aggregation question

This also works, I appreciate the help!