2 Replies Latest reply on Sep 3, 2012 1:57 AM by suresh.joshi.0

    How do I use a measure where its name contains a relative date?

    suresh.joshi.0

      Hello all,

       

      I'm trying to create a chart based on a source data extract that I have no control over. I have several measures, each of which contains the date which the measure was obtained in the title of the measure (relative to the current month).

       

      So, for example, I have 12 columns containing data to be plotted, however the column titles are:

      XYZ-1 ('Represent's the previous month - ie. July 2012)

      XYZ-2 ('Represents two months ago - ie. June 2012)

      XYZ-3

      ...

      XYZ-12

       

      However, I would like to plot each of those measures against dates, so does anyone know how I could link those measure titles to actual dates?

       

      Thanks,

      -Suresh

        • 1. Re: How do I use a measure where its name contains a relative date?
          Tamas Foldi

          The best would if you could do the unpivoting in database / query level. You should have a date dimension table joined with case-whens to your extracted data. Is it possible to use a date dimension table in your case? What database is used? Maybe when can help you how to transform the query.

           

          However, if you have a date dimension and your values in a SQL92 compliant server, then something like:

          SELECT

             CASE

               WHEN d.date = sysdate() - INTERVAL '1 MONTH' THEN XYZ-1

               WHEN d.date = sysdate() - INTERVAL '2 MONTH' THEN XYZ-2

             END measure,

            d.date

          FROM you_extracted_date e

          CROSS JOIN date_dimension d

          WHERE d.date in (sysdate()- INTERVAL '2 MONTH', sysdate() - INTERVAL '1 MONTH')

           

          Anyway, you can build the same query with tableau filters.

          • 2. Re: How do I use a measure where its name contains a relative date?
            suresh.joshi.0

            Hi Tamas,

             

            Thanks for that reply. Temporarily, I used some different date fields stored in my Excel source data, but I think for a 'correct' solution, I need to go the route you suggested.

             

            Thanks!

            -SJ