9 Replies Latest reply on Mar 13, 2013 6:28 AM by Brad Llewellyn

# Comparing Wk, MTD, YTD, YTG and FY to budget and LY.

Hi,

I am new to tableau and would appreciate some help. The software is being introduced in our company and I wanted to see if it’s easy to replicate some of our Excel reports.

I have attached a packaged workbook along with the excel file. The excel file has data tab which connects to Tableau workbook. It also has a fiscal calendar which we use and a sample output.

It’s a simple report which compares Wk, MTD, YTD, YTG and FY to budget and LY. Also it provides a week and day performance for past few and future (forecasted) few weeks.

While it’s simple in excel to do, I got stuck in Tableau to do following.

• Computing Wk, MTD, YTD, YTG and FY for TY and LY
• How to compute last few weeks or days.

Any help is appreciated.

• ###### 1. Re: Comparing Wk, MTD, YTD, YTG and FY to budget and LY.

Rajesh,

It's actually much simpler than what you are attempting.  Here is how you alter your graph to show YTD instead of daily sales.

Right-click "Sales" in the Text pane -> Quick Table Calculation -> YTD Total

You can even edit this table calculation to become MTD Total or YTD Growth.

How many of your issues does this solve?

Brad Llewellyn

Associate Consultant

Mariner, LLC

brad.llewellyn@mariner-usa.com

http://www.linkedin.com/pub/brad-llewellyn/4a/80a/73b/

http://breaking-bi.blogspot.com

1 of 1 people found this helpful
• ###### 2. Re: Comparing Wk, MTD, YTD, YTG and FY to budget and LY.

a couple...how do i do the past and future weeks

• ###### 3. Re: Comparing Wk, MTD, YTD, YTG and FY to budget and LY.

Rajesh,

For those, you must create your own table calculation.  The formula would be

LOOKUP( [Sales], -1 )

For the previous week and

LOOKUP( [Sales], 1 )

For the next week.  Also, you will have to make sure that these lookups are computed using your [Week] field.  If you don't know how to use "Compute Using", I have a guide in my blog post,

http://breaking-bi.blogspot.com/2013/02/working-with-table-calculations-in.html

Hope this helps,

Brad Llewellyn

Associate Consultant

Mariner, LLC

brad.llewellyn@mariner-usa.com

http://www.linkedin.com/pub/brad-llewellyn/4a/80a/73b/

http://breaking-bi.blogspot.com

1 of 1 people found this helpful
• ###### 4. Re: Comparing Wk, MTD, YTD, YTG and FY to budget and LY.

Ok...Using the Relative date method i got a lot of metrics i wanted, though on different tabs..

How to compute last few weeks or days: DONE

Wk, MTD, YTD,and FY: Done

YTG : Still struggling...is there a way to subtract YTD from FY.

• ###### 5. Re: Comparing Wk, MTD, YTD, YTG and FY to budget and LY.

Rajesh,

There absolutely is.  First, what are FY and YTG?  I could guess but it would be better to ask

Hope this helps,

Brad Llewellyn

Associate Consultant

Mariner, LLC

brad.llewellyn@mariner-usa.com

http://www.linkedin.com/pub/brad-llewellyn/4a/80a/73b/

http://breaking-bi.blogspot.com

• ###### 6. Re: Comparing Wk, MTD, YTD, YTG and FY to budget and LY.

FY: Full year

YTG : Year to go.

YTD" year to date

FY - YTD = YTG

Gautam

• ###### 7. Re: Comparing Wk, MTD, YTD, YTG and FY to budget and LY.

Rajesh,

Approaching YTD is slightly more complicated.  There are a few different ways to approach it based on what your report looks like.  Could you create a mock-up or some type of sample so that I can see what dimensions you will be using?

Thanks,

Brad Llewellyn

Associate Consultant

Mariner, LLC

brad.llewellyn@mariner-usa.com

http://www.linkedin.com/pub/brad-llewellyn/4a/80a/73b/

http://breaking-bi.blogspot.com

1 of 1 people found this helpful
• ###### 8. Re: Comparing Wk, MTD, YTD, YTG and FY to budget and LY.

here is where i am till date

• ###### 9. Re: Comparing Wk, MTD, YTD, YTG and FY to budget and LY.

Rajesh,

This workbook seems to be a sample workbook, which is perfectly fine.  Alas, there are a few issues.  Your "LY Sales" calculation is actually "Last Week Sales."  This is because you are asking it to look back one pane, and your table is at the week level.  Also, you are filtering out data before 3 weeks ago.  This will prevent you from being able to see YTD data or LY data because it is not visible to the worksheet.  That being said, I do not know whether these issues are present in your original workbook (which I assume you can not post here).  If you wanted to calculate a YTD sales value, you would have to use a WINDOW_SUM() function.  This would also require knowledge of "Compute Using", which I mentioned in reply #4.

Hope this helps,

Brad Llewellyn

Associate Consultant

Mariner, LLC

brad.llewellyn@mariner-usa.com

http://www.linkedin.com/pub/brad-llewellyn/4a/80a/73b/

http://breaking-bi.blogspot.com