4 Replies Latest reply on Sep 23, 2019 3:24 AM by Holly Affinito

# Date dimension filter - ytd

Hi - I've created a few layers of calculations and filters to get YTD in a workbook. It works when I have week ending date visible in the columns, however when I change this to year only the data is not aggregating correctly. Is anyone able to help me out with where I am going wrong here?

Thanks so much in advance

• ###### 1. Re: Date dimension filter - ytd

hi Holly,

I can't open your workbook ...seems to be a .twbr file in a .zip ...should be a .twbx

Having said that I expect this is due to you using the level of detail of week, in the calculation (along the lines of DATEPART('week', [your date]) <= DATEPART('week', MAX([your date]))

This means that Tableau needs the week level of detail in the viz to compute the Max as you expect. If you use an LoD you can tell Tableau what level to run the calculation over.

Something like

DATEPART('week', [your date]) <= DATEPART('week', {FIXED: MAX([your date)})

If that's not it, let me know and post a .twbx and I'm happy to take a look.

• ###### 2. Re: Date dimension filter - ytd

Hi - I've updated the workbook. I thought I'd attached a packaged workbook but clearly not!

• ###### 3. Re: Date dimension filter - ytd

hi Holly,

So as you are using a parameter to select the date, we can do away with LoDs.

I first created this...

[SR: Filter Last 2 Years]

year([Week ending date]) = year([Parameter 1])

or

year([Week ending date]) = year([Parameter 1])-1

bring onto filter shelf and set to true

and then this

[SR: Filter to YtD]

DATEPART('week', [Week ending date]) <= DATEPART('week', [Parameter 1])

bring onto filter shelf and set to true.

I think this is all you need.

Hope that makes sense, but let me know if not or if it fails somewhere.

1 of 1 people found this helpful
• ###### 4. Re: Date dimension filter - ytd

This is perfect - thanks