Yes. Post a sample packaged workbook. Use Superstore if data is confidential.

--Shawn

Thanks for the workbook. Unfortunately I struck out again. I must not be awake yet. Anybody else know how to do this?

--Shawn

Siddharth,

You can make a duplicate of your Data , then go in Data - Edit Relationships  and remove the relationship with  Customer Name.

This way you can filter the customer and get it's AVG(shipping cost) on the original data, and get the full  AVG(shipping cost) frome the duplicate data source.

Michel

A note on Michel's solution, based on your comment "...the data is large...": you don't necessarily have to duplicate all the data, you can just duplicate the connection (when using a live connection) or if you are using an extract, open up a connection directly to the data extract. That way there is still only one copy of the original data.

In the attached, I put together an alternative solution using table calculations. In this case only one connection is necessary and a table calculation filter is used. Since table calculation filters are computed after data is returned, if you have a large number of customers Michel's solution would probably be faster.

Jonathan

Jonathan

Thanks for the notes. Never thought of creating a double connection on a single extract.

I have a question regarding your alternative solution. Since a table calculation is involved , we need to add the Customer Name on the Detail shelf. Suppose i would like to compare the AVG(shipping cost)  for a Customer Name Set (the aggregated AVG , not each Customer AVG) , would it be possible to do it using table calculation.

In other words would it be possible to reproduce  the attached sheet 1 ,  using a single connection ?

Also , i am trying to understand what exactly PREVIOUS_VALUE()  add in your calculation.

PREVIOUS_VALUE(TOTAL(AVG([Shipping Cost])))

Wouldn't  TOTAL(AVG([Shipping Cost])) be sufficient ?

thanks

Michel

Hi Sid,

I'm presuming the initial workbook is a .twb and not a .twbx in the following:

- To duplicate a live connection, right-click on the data connection and choose Duplicate.

- To create a 2nd connection to an extract, use Data->Connect To Data, choose "Tableau Data Extract" from the "In a file" section, then navigate to your Tableau Data Extract. In this case, you do need to be careful about what calculated fields you create in which data connection, there can be some odd behaviors when the extract is refreshed that I haven't worked out the details of. Nowadays I try to avoid that by just creating all the calculated fields in the original, even though some of them might only be used in the 2nd connection.

Jonathan

Hi Michel,

Easy question first: I'm using PREVIOUS_VALUE() as a performance optimization. With Customer Name in the view and the table calculation addressing on Customer Name, that means Tableau is going to compute a table calculation result for every Customer Name. In a large data set this can lead to performance issues. PREVIOUS_VALUE() is a table calculation that takes as an argument an aggregate and only uses that for returning the result of the first row in the partition. After that, PREVIOUS_VALUE() uses the returned value of the calculated field from the prior row in the partition. So in this case, wrapping the TOTAL(AVG([Shipping Cost])) in the PREVIOUS_VALUE() leads to the TOTAL() being computed once for the first row in the partition, then that value is returned to every other row in the partition. Richard Leeke came up with this awhile back.

As for the view, that takes a little more work (and I found what could be a bug in Tableau).

- Like the previous table calc solution, we want to use a table calc filter on the set so we can have all the data to compute the total average Shipping Cost.

- However, we can't use the In/Out of set fields in aggregate calculations because they are boolean. To work around this, we wrap them in a calculated field like so: IF [Customer Name Set] THEN 1 ELSE 0 END, and then we can wrap that in a LOOKUP for the filter.

- Then we can use the same TOTAL(AVG([Shipping Cost])) calculation as before, with a Compute Using of the set field.

See the set crosstab and set view in the attached. The potential bug that I found is that we can't use the Set for a Compute Using, though it works fine in a Table (Down) and definitely works fine when wrapped in a calculated field as above.

Jonathan

Many thanks Jonathan,

          --PREVIOUS_VALUE() as a performance optimization...

Makes a lot of sense, I'll keep this one in mind...

          --IF [Customer Name Set] THEN 1 ELSE 0 END, and then we can wrap that in a LOOKUP for the filter.

Very instructive tip...

thanks again

Michel

It turns out that Tableau does evaluate TOTAL() only once for each partition, so we don't need to wrap that in the PREVIOUS_VALUE().

Thanks for the update.