2 of 2 people found this helpful
How are you trying to show this information? I'm going to assume returning a single value. This same concept would work for graphs but I'll use it as if it were a text table. IF you want a graphic instead of a text table also drag the measure to the rows shelf in the second step.
1) The easiest way is to drag your date field to the columns shelf and change it to using the week option.
2) After that Drag your measure to text.
3) At this point you have a ton of columns and a single row. Never fret.
4) Right click your measure and change it to using quick table calculation and choose % difference.
5) Now make this calculated field
LAST() = 0
It's a single statement so it is a boolean. It will either return true of false based of the calculation LAST() - more on that later.
6) Put this onto the filters shelf and set it to only allow true values.
To learn more about the table calculation LAST() I used check out this post. Its quite informative. https://www.interworks.com/blog/tmccullough/2014/09/29/5-tableau-table-calculation-functions-you-need-know
Do you know of a way to still show the original data for all the previous weeks at the same time and just tag this onto the end of a data table?
1 of 1 people found this helpful
By original data you mean the actual weekly values? But you want the last week to show the actual and the % difference from the prior week? If so do not use the LAST() = 0 Filter.
Instead create your calculated field for the weekly change. Then create a calculation that says...
IF LAST() = 0 THEN ^^^^^Insert the the table calculation from above into here. You can do this by creating the calc and dragging it into the calculated field window^^^^ END
This will (only for the last field) return this % difference and for the others return null.
Then also use your regular sum(sales) on the text shelf with it or on the rows shelf and put the above as a label.
The inserted section from above will look something like this.
(SUM([Measure]) - LOOKUP(SUM([Measure],-1) )
Am mobile so please excuse syntax error iphone is not forgiving.
Thanks Carl, I don't know if I'm doing something wrong or if its just not possible to get it the way I want it. I just seem to get a little lost following you and how to create this. I have included a sample file of how far I have got, I am using the superstore sample data as I cannot share my own data, and below I have an excel screenshot of how I would like it to look if it's possible. If only we could write our own formulas for grand totals that would be ideal as essentially I think that's where I would like to to be.
I'm out working with clients and then am attending a user group so my reply
will be tragically slow for you. However you sort of can make a separate
formula for grand totals. In this case it's really no needed I don't think.
I'm unable to open the workbook currently but a sneaky way to define a
formula for the totals is to say.
If First()=Last() then ....grand total formula....
Else .... Other formula.....
However I don't Reccomend doing this unless you understand what each bit
On Monday, 22 August 2016, Jamie Morgan <email@example.com>
I think the easiest way to achieve what you’ve shown in the excel table would be to have separate weekly change calculations for each of your measures, then display them in a separate worksheet to your yearly totals and place the two worksheets adjacent to each other in a dashboard like so:
The Week on Week calculation takes the SUM() of a measure for the latest 7 days in the datasource and subtracts the SUM() of a measure for the 7 previous days then divides by the previous 7 day’s figure. That’s what this calculation does, note that if you wanted to look at complete weeks you’d need to use DATETRUNC() to get the beginning of the current latest week and adjust the calculation to return values within 7 and 14 days of that “beginning of latest week” date.
Please see the attached workbook for a demo of the latest 7 days method, please get in touch if this isn’t the solution you were looking for.
I have tried to avoid the dashboard workaround as it doesn't allow scrolling down long sheets of data.
I don't know if I would be able to combine your answer with Carl's for the grand total to give the output that I'm looking for?