5 Replies Latest reply on Jan 18, 2017 8:28 AM by Eric Saragosti

    How to create a sliding filter by period (YYYYMM)

    Eric Saragosti

      Hi Guys,

      In our reports, we tend to store our dates in the format yyyymm in numeric format. I've created a sliding filter for them.

      The filter is set to only use relevant data. However when I look at the filter, it has all values from 200101 to 202099.

      How do I set the slider to only have values that are in the table?

       

      Thanks!

        • 1. Re: How to create a sliding filter by period (YYYYMM)
          Jamieson Christian

          Eric,

           

          Can you attach a packaged workbook that illustrates your problem? It's hard to tell what's going on just from the description. If you set your filter to use Relevant Data, then that tells me that some of your data include "200101" as the date. Without seeing your data, though, it's hard to confirm or propose a workaround.

          • 2. Re: How to create a sliding filter by period (YYYYMM)
            Jeffrey Kritzman

            Hi Eric,

             

            There are probably values for all of those values in your view, but it's hard to tell just from your description.  If you attach a packaged workbook, we could better diagnose the problem.

             

            One troubleshooting idea - try putting the yyyymm field on columns and Number of records on rows, and see exactly what dates are in your data set.  You may be pulling in more than you think.

             

            Best,

            Jeff

            • 3. Re: How to create a sliding filter by period (YYYYMM)
              Eric Saragosti

              Hi Jamieson,

              Uploading the workbook is frowned upon by company policy unfortunately.

              However, I may have misexplained my issue. Records should start around 200101. The problem is that after 200112 is 200113. I've checked in the DB and there definitely aren't any values that don't make sense as dates.

              I assume that it's Tableau filling in the extra values.

               

              Eric

              • 4. Re: How to create a sliding filter by period (YYYYMM)
                Jeffrey Kritzman

                I see, that's a horse of a different color altogether!  You should consider making an actual date out of your yyyymm date field using a calculation along the lines of:

                 

                MAKEDATE(INT(LEFT(STR(yyyymm),4)),INT(RIGHT(STR(yyyymm),2)),1)

                 

                This should hopefully clear it up.

                 

                For future reference, you could always make a sample worksheet with fake data.  That's what I do, as my work is confidential.

                • 5. Re: How to create a sliding filter by period (YYYYMM)
                  Eric Saragosti

                  I actually already have a date field. For issues like this, I created a table that I called a date matrix with the months from 2000 to 2030 with first day of month, last day of month, period in YYYYMM, and an index to make it easier to add/remove month as well as the financial year. I join this to any table that I need to do date calculations on, it's faster and easier.

                   

                  I took the start date of every month, converted it to format Year-Month (custom calculation). However when I add it to the filter, the only options are a single value slider. I'd like there to be a slider that accepts multiple values/a range.