Michel, in the future it would be helpful if you included a full set data, not just a single row. I filled out your data arbitrarily:
Product CurrentWeek CurrentMonth CurrentQuarter YTD YTD-1 Product A 150 1,000 3,000 18,000 17,485 Product B 160 1,500 3,500 25,000 45,000 Product C 170 2,000 4,000 32,000 72,515 Product D 180 2,500 4,500 39,000 100,030
From here you can create a calculated field to work out the variation:
And then format it as a percentage. (See attached.)
But having said all this, I suspect that the attached worksheet isn't really how your data is actually shaped and the file you attached was the desired out come. If this is the case please post a small sample of your actual data.
Michel.twbx.zip 8.9 KB
Good morning Shawn and thanks a lot for looking into the issue.
I need help in figuring out the sum for the dates... The final calculation (the % YTD vs. YTD-1 is pretty easy).
I would like to be able to create calculated fields that handle the sum for different periods of time (this week, last month...)
What is the calculation formula to have a column that displays the current week totals? the current month totals, the current quarter totals...? This is where my headache is - if I may say so -
@Michel - I can't give you an answer without seeing your data, because the aggregations will depend on the format of your data. Please post a packaged workbook with a useful number of rows of sample data. Alternatively, you could point to some fields in the Superstore Sales data set that comes with Tableau.
@Shawn - I wouldn't call myself the "text table king," more a "slightly deranged text table experimenter."
New title noted!
Good morning Jonathan and Shawn ... looks like we are old pals here ;-)
As per your suggestion, I am attaching sample data using a Packaged workbook.
The DLRCD code is our customer number... In this sample, the desired outcome is a table that has each customer isn a row and a set of columns created using calculated fields where each column corresponds to a period of time.
I would like to regroup the different number fields (let's say for instance LINES) and determine how many lines where shipped
- Column 1: THISWEEK (using the Weeknum of the date the tableau is opened)
- Column 2: LASTWEEK (using week of current date - 1, I suppose)
- Column 3, THISMONTH (sum of all the lines done on the current month, using today's date as the way to determine the month number...)
I imagine you see the jist of it...
Thanks a bunch for showing me a couple of examples and I will use your suggestion to extend to other date time calculation.
SampleData.twbx.zip 178.4 KB
The easiest way for me to do this is to build some row-level calculations and surround them with a SUM(), see the attached for details.
row level calcs.twbx.zip 194.5 KB
Thanks so much Jonathan:
This one direction will give me opportunities to explore... I checked the calculations you have made (using Anchor Date to have a milestone / starting point)... I will duplicate your method no later than this afternoon / tomorrow and come back to you.
As usual you deliver!