Thank you for your answer. The problem with this is that if I remove the 'all' in the Shop Name, I cannot use the 'Shop Location' filter anymore as it will only show me the location linked to the shop name I chose.
Ah...in that case you can create an Aggregated Calculation something like
[Single Shop Filter]
COUNTD([Shop Name]) = 1
and bring this into the filter shelf for any sheets in your Viz, and set to True. This should then collapse the sheets, when more than one shop is selected.
Let me know if that doesn't make sense and I'll work up an example.
This could work.
I tried using this formula on the column 'Shop Id' but it still gives me all values despite having 'True' as filtered.
1 of 1 people found this helpful
Apologies Audrey...my bad, of course as you have Shop ID in the VizLoD each COUNTD would get calculated against each ShopID...so they are all 1, and nothing ever gets filtered out. I'm putting that down to it being a Friday (I think my brain is already fully in weekend mode!!)
So what I should have proposed...is the same method, but to use a WINDOW_SUM so we get a COUNTD of all ShopIDs in the Viz
[Filter for 1 City]
and then set this to True....in my example (attached) the default compute using (Table Down) does the job, but depending how you Viz is set up you may need to define the Table Calcs compute using.
No worries! The weekend fever is close
It might have gotten me too... I can't open your workbook. I tried using your formula in mine though but it seems that I cannot apply it to 'all worksheets using the data source'.
I tried using your formula in mine though but it seems that I cannot apply it to 'all worksheets using the data source'.
...no you won't be able to, as Table Calculation filters don't allow this option. You'll need to add it to each sheet you want to collapse separately.
Let me know if you can't get it working and I'll work up a multi-sheet dashboard example (also if you can let me know the version you are using, I can create an open-able version for you)
Ah this is why. Pity I have more than 20 sheets
But even on my sheets, I typed 'WINDOW_SUM(COUNTD([ShopId]))=1' but I only see 'True'. Even if the 'All' is selected in the filter and that I have in my table a lot of ShopIds.
We are on 9.2.
Does this work on any graph? Even if is not crosstabs?
I would not be against a multisheet example. Thank you!
1 of 1 people found this helpful
I've no longer got 9.2, but have created the attached in 9.0, so you can open it....
So in this I've used City as your ShopID, and state as the selector to filter down the cities.
Notice that I have City in the VizLoD of every sheet. We need this so the Table Calc can do it's thing, over this dimension. Without the filter and with all (or multiple cities) selected the Viz looks like a train-wreck! but that's not a problem as the user will only ever see the single city version.
In the Cross-Tab, where I have Sub-Category, also, in my VizLoD I needed to change the default compute using on the Table Calc filter to City (else it's doing the WINDOW_SUM over every City/Sub-Cat combination). I also had to hide the sheet titles in the dashboard, else the sheets don't collapse fully. If you need titles, then make them as sheets (with the same City in VizLoD as the others) and add the Table Calc filter and they will collapse and expand too (using containers so they fit over the top of each Viz)...You could even create a Viz, which collapses on the opposite (i.e. when 1 city is selected) and expands when All is selected with some text for the user ('Please select a city'...or similar) for a real polish!!
Hope this all makes sense?
Sorry for my late answer and thank you very much for your detailed info! I tried the formula just like it is in your workbook. For some reason, when I select 'true' it takes off some of the data but not all of it and when I put 'false' it shows everything....
In any case, I am not sure whether this approach is ok for my workbook where I literally have more than 20 sheets for my dashboard....
For some reason, when I select 'true' it takes off some of the data but not all of it
...So this sounds like you have another dimension in your Viz LoD...and need to amend the Compute Using for the Table Calculation (and may need to be set up differently sheet by sheet depending on their VizLoD0. In my example
I've set the Compute Using to be the 'thing' I want to count. When you first drag a TC filter onto the filter shelf Tableau will 'guess' at the Compute-Using you want (generally Table Down/Across) and bring you up the options for that. If you then go back into the Compute Using to change this (as I did to change to City) it brings back the filter options again based on the new Compute Using. If True isn't an option (as it might be that the filter is set to one city) you can use the custom value filtering to manually enter True.
In terms of another option, not using Table Calculations, it's tricky as you want to assess the Count at a grain lower (all cities) than you have in your Viz. You could use an LoD, but you'd need to have the filter as a context filter (so it got recalculated when the user filtered) which would cause an issue with performance. This way you'd have a formula which wouldn't need to be manually added (and set up) for each sheet, but there would be a hit on performance. I've attached a version of this in case it's of any use.
Hopefully you understand the reason Tableau works this way...in 99% of cases when you select 'All' you want to see 'All', and not nothing!!...so it's right we need to find a creative solution for the 1% not the 99%
hi again! I think I found what was bugging. It is because on my cross table I also have colors based on the type of shop (is it a competitor or is it ours). If I remove the coloring it works but with it it does not.
Do you have an idea on how to remove this?
For the context filter, considering the report and the data I believe it will be pretty bad in terms of performance. I would rather stick to using the window sum you proposed.
Yes so anything added to Colour/Detail/Size...etc. will add to the Viz LoD. This is similar to the 'Table' worksheet where I have Sub-Category and City in the VizLoD. So you should be able to set it up as per my screen shout above, by setting the Compute Using to be the thing (Shop name in your example).
Let me know if that doesn't do the trick, and we can delve into the advanced set up.
I tried setting it up to Shop Name but it didn't change anything