3 Replies Latest reply on Sep 12, 2019 5:49 AM by Simon Runc

    Date dimension filter - ytd

    Holly Affinito

      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
          Simon Runc

          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
            Holly Affinito

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

            • 3. Re: Date dimension filter - ytd
              Simon Runc

              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.