9 Replies Latest reply on Mar 21, 2017 12:47 PM by vibol chea

Need help with Previous YTD

Hello Tableau Community,

I've been trying for the last 24 hours figuring how to limit Previous YTD data to just up to February of 2016 since March isn't done yet.

Lets say:

2016

Month     amt

Jan          100

Feb          155

Mar          200

2017:

Month     amt

Jan          120

Feb          145

What I want to view:

YTD     Prev YTD

265      255

thanks,

Vibol

• 1. Re: Need help with Previous YTD

Hello Vibol,

please find the above attachment of YTD and Prev YTD calculations  in 9.3v workbook...

Sravan

1 of 1 people found this helpful
• 2. Re: Need help with Previous YTD

Hi Vibol,

You can try these formulas:

I have attached the workbook for your reference. Let me know if you have any questions.

Thanks,

Mithil

• 3. Re: Need help with Previous YTD

Hi Vibol,

The best way to solve this is to understand your Tableau function called LAST(). Using visual analytics you could:

1. Drop you Date Dimensions into Rows:

2. Double Click on your Marks canvas and type LAST()

3. Drop LAST() to Text

See the behaviour. The most recent month of your record will always be 0.

With that, you can do different logical tests to get the data you want.

E.g.  Double Click on your Marks canvas and type LAST()>0 AND LAST()<13

Drop LAST()>0 AND LAST()<13 to Colors

This boolean will be true for 12 months data that does not include the recent values.

You can then use the conditions to filter data or perform other calculations. E.g.

IF [LAST()>0 AND LAST()<13] THEN SUM(Sales)

• 4. Re: Need help with Previous YTD

Thank you for your replies....I will try them all, but I forgot to mention that I will need Differnce% also.

Right now I use calculated fields and I will have create 20 plus fields...ouch!! I would love it to auto by table calc?

• 5. Re: Need help with Previous YTD

Hi Vibol,

I will approach your problem using similar visual analytic process. This is why I love Tableau because you can visualise what you want to do. As a rule of thumb, understanding is more important than technique, and thus if you understand how Tableau function works, you will be able to do miracles with them.

The functions I will consider using are LOOKUP() and Previous_VALUE().

What you can deduce from that is that you are in control of the calculation. E.g. Double click on the Measure Values and add below calculation ((New-Old)/Old):

(SUM([Sales])-LOOKUP(SUM(Sales),-1))/LOOKUP(SUM(Sales),-1)

• 6. Re: Need help with Previous YTD

Hi Prayson,

Do you mind sharing your workbook?  Also, I have over 20 different values not just SALES.  Does that change anything...

thanks,

Vibol

• 7. Re: Need help with Previous YTD

My reply above has Book1.twbx  workbook file attached to it.

For your second question; It does matter how many values you have. If you can make a dummy dataset, that is similar to your actual dataset, and explain what you want to show and how, I will be more than glad to assist you.

• 8. Re: Need help with Previous YTD

Hi Prayson,

My dataset does not have dimensions I can use to report...only on these measures ,for example, the Superstore has Qty, Sales, Profit, discount ...except my data do not have anything in similar.

I would to format it like the image below.

• 9. Re: Need help with Previous YTD

Also if you could show me how to get the difference % that would just make my day!

I did try table calc but i dont like how it adds a row to each of the dimensions...Tried calc field and it did not produce anything.