A tooltip shows data available for that specific mark. That's why you see only one year. But you COULD make separate calcs for each year that you want to show. It would take x-many calcs to show x-many years though.
On the flip side, you could use the new sheet-in-a-tooltip feature of V10.5. the sheet you show could show all yearly data for a given dimension for that mark, for example.
Thanks....what would that calculation look like? I know that tooltips are
for a specific mark, but in this instance I want to show (the below is made
Sales (2016): $100
Sales (2017): $150
Yes, you bring up a good point on the 'sheet' within the tool-tip, but do
you know what the calculation is to do the above??? We don't think the UX
with sheets in tool-tips is always good.
On Tue, Jun 5, 2018 at 3:16 PM, Joe Oppelt <email@example.com>
Part of that answer is going to depend on what your sheet looks like. If you are doing a year-by-year chart, then something like this:
your current mark would have SUM([Sales]) on it, and the other calc would grab the sales from one mark back. And another calc
(SUM([Sales])-[that lookup calc])/SUM([Sales])
would give you the YOY % Diff
This is going to depend on what your sheet really looks like though. if you need help with specifics, I'm going to need a sample workbook to work with.
Thanks Joe. Do you not see the two attachments that I posted with the original post? One is a screenshot and the other is a packaged workbook using superstore data with what I am trying to do.
Can you view these?
OK, I missed seeing that earlier.
So I'm looking at Sheet1(3) which is the barchart from the dashboard. I guess for demonstration purposes, you want to see the % change of (for example) the 2016 sales on the 11th versus the 2017 sales on the 11th.
First, to make sure I'm playing with the right deck of cards, I made sheet 4 that just displays sum sales by day for the two years. Then I added the quick table calc you had on your sheet. And there is your % diff. But let me take this further.
On copy 2 I made my own calc. I like to use my own calcs for stuff like this. I often find that once we start building the viz, the user asks for next steps, and we need to use the calc in other calcs... Having my own calc to drag into things really helps for that. I could have hacked up my own syntax to take the differences and divide, etc. But a quick way to make your own calc that mirrors tableau's quick table calc is to open the calc editor and just drag the quick table calc pill into the editor. Just drop it in there. You can see what tableau inserted in there for me. (All the extraneous ZN() and ABS() stuff is sometimes superfluous, but it does have its purpose. ZN() creates a zero value if the actual value is NULL, for example. It could be important at times. Just leave it all in there.)
So there is my own calc. You see that it generates the same values. Look at how Tableau is getting the prior year stuff. LOOKUP() looks at whatever cell you tell tableau. I used -1. That looks backward one hop. In fact, tableau is doing this for both 2016 AND 2017 columns. But there is no -1 cell when we're in 2016, so the result is null. That's why we only see data in the 2017 column for this field.
Still on copy 2 sheet, right click on the %diff calc and select "edit table calc". Tableau put this on the sheet with a default of TABLE(across). That means that for each row, evaluate this calc across the table. For Day 1, 2016, there is no -1. For 2017 we get a value. The go to the next row and do the same. Etc.
On copy 3 I did something important, even though the sheet looks exactly the same. Edit the table calc for %diff like you did on the previous sheet and look at the addressing now. It looks like this:
Now I'm telling it a specific order in which to evaluate the marks rather than relying on the table layout to determine that for me. Note also the "restarting every" setting. Specifically do this for each day. It's the same result as the default for this sheet, but I'm going to be moving things around, so I want control of how the calc operates.
GO to copy 4. I simply took the sum(sales) off the sheet. These are eventually the bar values you will want to see.
Go to 5. I moved YEAR off columns. This would have broken the results of %Diff if I hadn't specified the table calc order.
Go to 6. I reshaped the sheet. DAY is now on columns, and %Diff is the rows. The numbers again stay intact for me.
Sheet 7 is the bar chart. I did several new things here. Look at the filter. I put a param for the user to specify a year, and made a calc to grab my two years instead. Easier for the user that way. Also (and this is really what you are ultimately looking for) I made a prior year calc so we can display the value in tooltips. Both years are still on the sheet, BTW, but there are only values for 2017, so that's all the chart you will see. (Notice the 31 nulls at the bottom right of this sheet. Those are the 2016 marks, all null.) To get the prior years, I cut the LOOKUP logic from the %Diff calc that Tableau wrote for me back on Sheet (2). Its a table calc, and I set the settings to match what we have for the %Diff calc.
Go back to Sheet 3 at your convenience and play with the settings of the table calc. Pick Table(down) for instance and see what it does. This is an enlightening exercise if you need help understanding what these settings do.
Anyway, there you go. There's a lot in here and I'll be glad to explain any point that wasn't clear to you. And I'll also help you when you try to implement this in your actual workbook.
test for Jessica.twbx 1.3 MB