-
1. Re: Can i have a Dual axis chart but with a filter applied only on one?
Shawn Wallwork Apr 24, 2013 5:01 PM (in response to Siddharth Surana)Yes. Post a sample packaged workbook. Use Superstore if data is confidential.
--Shawn
-
2. Re: Can i have a Dual axis chart but with a filter applied only on one?
Siddharth Surana Apr 24, 2013 5:37 PM (in response to Shawn Wallwork)Shawn
Thanks a lot for the reply
Here is an attached workbook. All I want is to compare the same metric for all customers (Average) to one specific customer. Also please note that as the data is large I will not be able to do any sort of self join. So if there is any other approach it would be great.
Thanks
Sid
-
Filter sepeartely.twbx.zip 940.3 KB
-
-
3. Re: Can i have a Dual axis chart but with a filter applied only on one?
Shawn Wallwork Apr 25, 2013 6:03 AM (in response to Siddharth Surana)Thanks for the workbook. Unfortunately I struck out again. I must not be awake yet. Anybody else know how to do this?
--Shawn
-
4. Re: Can i have a Dual axis chart but with a filter applied only on one?
Michel Caissie Apr 25, 2013 6:42 AM (in response to Siddharth Surana)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
-
Filter sepeartely.twbx.zip 1.8 MB
-
-
5. Re: Can i have a Dual axis chart but with a filter applied only on one?
Siddharth Surana Apr 25, 2013 8:09 AM (in response to Michel Caissie)Hey Michel
Brilliant. Thanks a lot for the answer. I am going to do this at present but don't you think Tableau should be able to do this without the need to duplicating data. Specially when Tableau supports really large data sets?
Nonetheless, I am going to incorporate this for the moment and thanks for your support and quick response
Regards
Sid
-
6. Re: Can i have a Dual axis chart but with a filter applied only on one?
Jonathan DrummeyApr 25, 2013 8:11 AM (in response to Michel Caissie)
1 of 1 people found this helpfulA 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
-
7. Re: Can i have a Dual axis chart but with a filter applied only on one?
Michel Caissie Apr 25, 2013 10:43 AM (in response to Jonathan Drummey)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
-
8. Re: Can i have a Dual axis chart but with a filter applied only on one?
Siddharth Surana Apr 25, 2013 11:09 AM (in response to Siddharth Surana)Thanks Jonathan
I wanted to know what it means by " 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" ? Could you please elaborate on this or maybe provide a link that explains this. I have 2 workbooks, one with the extracts and one which has a live connection and would want to do this for both of these
Thanks
Sid
-
9. Re: Can i have a Dual axis chart but with a filter applied only on one?
Jonathan DrummeyApr 25, 2013 11:29 AM (in response to Siddharth Surana)
1 of 1 people found this helpfulHi 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
-
10. Re: Can i have a Dual axis chart but with a filter applied only on one?
Siddharth Surana Apr 25, 2013 11:34 AM (in response to Jonathan Drummey)Thanks a lot Jonathan
That is pure brilliant. Also Could you answer\ Michel's question, just the one before mine?
Thanks for all your help
Regards
Sid
-
11. Re: Can i have a Dual axis chart but with a filter applied only on one?
Jonathan DrummeyApr 25, 2013 12:22 PM (in response to Michel Caissie)
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
-
12. Re: Can i have a Dual axis chart but with a filter applied only on one?
Michel Caissie Apr 25, 2013 12:47 PM (in response to Jonathan Drummey)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
-
13. Re: Can i have a Dual axis chart but with a filter applied only on one?
Jonathan DrummeyMay 9, 2013 6:35 PM (in response to Michel Caissie)
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().
-
14. Re: Can i have a Dual axis chart but with a filter applied only on one?
Michel Caissie May 10, 2013 7:31 AM (in response to Jonathan Drummey)Thanks for the update.