Hi Paul! Someone else may know better, but I don't think there's any direct way to reference a worksheet in a calculated field.
However, I think you could create an indirect workaround using a secondary data source. All you'd have to do is create a data source with as many rows as you have worksheets. Then, make each row distinct. You would join that data source to the primary data source, then filter it down to the value of one different row for each worksheet. You could then use ATTR([Secondary Data Source].[Value]) to check which worksheet it is.
If this doesn't work (as I haven't tried it yet), you could try duplicating rows in your primary data source, add the differentiating dimension there, and then filtering down to just one set.
If I have time in a bit, I'll try to create a sample workbook to demonstrate this.
I believe you can't do it.
What you can do is to create a visualization based on parameters selection and use this selection to control your calculation.
Thanks. Unfortunately a parameter is user based and will control all worksheets (on one dashboard) at the same time.
Hmm...yes please...you have lost me
1 of 1 people found this helpful
Here's an extremely simplified example. I created an excel workbook that only contains the below cells and then added it as a data source, naming it "Sheet Filter":
Sheet Profit Sales
I had to create a dummy dimension in both the primary and secondary data source to use blending on these. It's just called "All", and its value is just "All". Otherwise, Tableau wouldn't allow a blend.
Then, I created two sheets: Profit and Sales. They both just show the value of this calculation:
IF ATTR([Sheet Filter].[Sheet]) = "Profit" THEN SUM(Profit)
ELSEIF ATTR([Sheet Filter].[Sheet]) = "Sales" THEN SUM(Sales)
In the Profit sheet, I added [Sheet] to the filter shelf and filtered in only "Profit". When this happens, the calculation sees only "Profit" from the secondary data source, so it does SUM([Profit]).
In the Sales sheet, I did the same thing except that I filtered in "Sales". You can see that the output is different.
Tableau 9.3+ workbook attached.
Thanks David. That's a very interesting approach, I'm going to try it (on a much more complex workbook).
You're welcome! Let me know if it works out.
David/Paul, it was not working very well when you show filter on panel.
This approach is similar data island concept on qlikview development. I never used because i don't know how warnings of data blend behave on server.
Do you know what happens?
I did an improvement if you don't mind.
I get the following error when trying to implement. I guess I need to first publish the excel as a datasource before Tableau server accepts it?
No Tableau Server user found.
That's probably the issue, yes.
This is kind of a "brute-force" approach, but why not just create a different calculated field for each sheet? So you would have a seperate calculated field for each formula, formula1, formula2, etc. And then just include the appropriate calculated field on the appropriate worksheet. Creates some clutter - lots of calculated fields - but I think it would solve your problem, wouldn't it?
Yes, I will probably do that. It is just that it will explode the # of calculations...