6 Replies Latest reply on May 31, 2016 10:12 AM by Ivan Young

    Dynamic Date Header

    Apeksha A

      Hi,

       

      I need assistance with the date header. My report displays data variance for Current Data - 2 ie Day before yesterday's data & Last month same day data.

      eg: If current date is 5/27/2016 then report displays values for 5/25/2016 and 4/27/2016 and  does a comparison which refreshes everyday

       

      I have all the calculations ready however want to replace Measure Names with their actual dates

       

      eg: If current date is 5/27/2016 output should be what is highlighted in RED

      Attached sample workbook for your reference

       

      Thanks a lot

      Apeksha

        • 1. Re: Dynamic Date Header
          Nagarajan R

          Hi Apeksha,

           

          Gave a shot at this. Below is what I came up with. I substituted today() with a parameter as I did not have actual/current year data to display. You may replace parameter with Today() in your case.

          To achieve the output, I created only two additional calculated fields

          1) Dynamic Date Filter (Added this to filter shelf and selected 1)

          IF [Order Date]=[Select Date]-2 OR [Order Date]=[Select Date]-30 THEN 1

          ELSE 0

          END

          2) Sales by Dynamic Date

          IF [Order Date]=[Select Date]-2 THEN [Sales]

          ELSEIF [Order Date]=[Select Date]-30 THEN [Sales]

          END

           

          You may extend this logic to exactly suit your requirements if it looks relevant to your ask.

           

          Thanks

          Nag

          • 2. Re: Dynamic Date Header
            Ivan Young

            Hi Apeksha,

            I made a few mods to your worksheet and I think you can get the layout you desire.  A brief desc of what I did.

             

            1. Created a date header dimension using your calculated field logic.

            IF [Calc_yesterday's Data] > 0 THEN [Date Day Before Yesterday]

            ELSEIF [Calc_Same Day Last Month Data] > 0 THEN [Calc_Same Day Last Month] END

             

            2. Created a single measure to work with the date header field.

            IF [Kpi Key] = 1 THEN [Amount] ELSE [Count] END

             

            3.  Drag DateHeader field to filters and exclude nulls.

             

            4. Layout per screenshot.

             

            Let me know if you have any questions.

             

            Good luck,
            Ivan

             

            PS.  I added data to your count field which is why you no longer see zeros

             

            • 3. Re: Dynamic Date Header
              Apeksha A

              Thank You so much. I shall try both the solutions and update what worked

               

              Thanks again!

              • 4. Re: Dynamic Date Header
                Apeksha A

                Thanks Ivan..It worked for these 2 measures however if i need to do variance and % variance from these 2 measures then this formula doesn't work..is that correct ?

                 

                eg: Variance:- Sum([Calc_Yesterdays_Data]) - Sum([Calc_Same Day Last Month Data])

                 

                % Variance :- [Calc_Variance]/Sum([Calc_Same Day Last Month Data])

                 

                Thanks,

                Apeksha

                • 5. Re: Dynamic Date Header
                  Nagarajan R

                  Hi Apeksha,

                   

                  Just curious to know if you tried the option I gave.

                  I was not able to open your workbook as I was running on a lower Tableau version and hence mocked up on sample data.

                   

                  Was my understanding of your requirement wrong?

                   

                  And @Ivan Young - Interesting solution. Thanks

                   

                  Thanks
                  Nag

                  • 6. Re: Dynamic Date Header
                    Ivan Young

                    Hi Apeksha,

                    You can use table calcs to get the variance.  You'd probably want to alias the measure names.  Alternately you could put your variance calcs in another sheet to avoid repeating headers for the measure names.  Let me know how it works.


                    Ivan