7 Replies Latest reply on Oct 16, 2018 6:41 AM by Gerardo Varela

# Comparison with LY and having the date in dimension

Hi the community,

I am new on Tableau and I've been stuck with an issue since 1 week.

I would like to create an measure LY (Last Year) in a tab and have the date as a dimension of this tab. I checked a lot of post about last year comparison in the forum but no-one had the date in dimension of the tab.

Example:

Here there is the Month in dimension but this should work also when the year only is displayed or also the exact date.

Thanks a lot.

Regards,

• ###### 1. Re: Comparison with LY and having the date in dimension

Hope this helps,

1) Create a calc field "Prior Year Sales" like below and add it to the view.

or if you have dates in descending order like in the screenshot, change the offset to 12 instead of -12.

Best,

Shiva.

• ###### 2. Re: Comparison with LY and having the date in dimension

Hi Shiva,

Thanks for your answer. Unfortunately, it won't works for me as if I want to reduce my dimension at Year level or expand it at daily level, the lookup -12 will be false.

Regards,

• ###### 3. Re: Comparison with LY and having the date in dimension

Interesting problem, and I must admit I learned something new thanks to you.

These two threads should definitely have answers to what you're looking for,

Dynamic Offset Value to Flow with Hierarchies  -- Uses Parameter approach by swaroop.gantela

Prior Year Running Totals -- Calc field approach by Gerardo Varela

I've used Gerardo's approach and added couple more layers to give week and day,

Year,

Quarter,

Month,

And so on,

Hope this helps.

Best,

Shiva.

• ###### 4. Re: Comparison with LY and having the date in dimension

Hi All,

Glad you found my other post useful! Since we aren't doing different calculations at different hierarchy levels all you have to do is set LOOKUP(SUM(Sales),-1) at the year level.

Workbook  vs 2018.2 attached.

Regards,

Gerardo

1 of 1 people found this helpful
• ###### 5. Re: Comparison with LY and having the date in dimension

Awesome! This is neat. So when I added date level and set the offset to -366. The prior year for day starts somewhere in March and not January, this is because there are few dates missing in the data set. I was banging my head on how to solve this problem and you did this with one line of code.

• ###### 6. Re: Comparison with LY and having the date in dimension

Hi Guys,

However do you know if it exist a solution without using lookup function ? I want to create an expression that we can use in all type of object and the lookup is difficult to manage for every cases. Moreover, here we use a comparison with last year same date , and I wanted also to create another expression which will be a comparaison with last year but at the same day of the week (so not doing dateadd('year',-1,order_date) but by doing dateadd('day',-364,order_date) )

Any ideas ?

Thanks again for the help.

Regards,

Jeremy

• ###### 7. Re: Comparison with LY and having the date in dimension

Hi Jeremy,