8 Replies Latest reply on Jan 22, 2013 9:44 AM by Michel Khennafi

# Different date Calculations

Good day to all:

I would like to create a table where for any given product X (rows) I have several columns with the sales number generated

- The current week (Column 1)

- The current Month (Column 2)

- The current Quarter (Column 3)

- Year to date (Column 4)

- Last Year for the same year to date (Column 5)

- YTD - (YTD-1) % variation (increase or loss in %)

I have tried many formulas but never gotten the appropriate results...

Has someone created the same type of tableau? Has anyone some examples to share?

See my attached example:

I appreciate your patience and support

Thanks to All,

MK

• ###### 1. Re: Different date Calculations

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:

(SUM([YTD])-SUM([YTD-1]))/SUM([YTD-1])

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.

--Shawn

• ###### 2. Re: Different date Calculations

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 -

• ###### 3. Re: Different date Calculations

Michel, I see what you mean. So I'm pinging Jonathan, the text table king.

--Shawn

• ###### 4. Re: Different date Calculations

@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."

Jonathan

• ###### 5. Re: Different date Calculations

New title noted!

--Shawn

• ###### 6. Re: Different date Calculations

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.

• ###### 7. Re: Different date Calculations

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.

1 of 1 people found this helpful
• ###### 8. Re: Different date Calculations

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!

Michel