6 Replies Latest reply on Feb 23, 2018 12:40 PM by Rommel John Gammand

    Compare same day previous week

    Rommel John Gammand

      Hi,

       

      I'm new to this forum. I'm trying to figure out how to build below chart using the attached spreadsheet data. The number in the chart indicates the difference between latest date and same day last week. Appreciate your help.

       

      Thanks,

      Rommel

        • 1. Re: Compare same day previous week
          Andrew Bickert

          Hi Rommel,

           

          You could use the quick table calculation of difference to achieve this. When you add your measures to the Measure Value area, go to Quick Table Calculation>Difference. The two dates you have in your row area will have the difference calculated between the two.


          To get the current date and a week past, you could build a filter for a maxdate([date]) to get the most recent date in your data set and then do a DateAdd Cacluation (DATEADD('day',-7,max([Date]))) to get the date from there. You could also use parameters for your Last Week and Current Date and compare the dates to those values (

          IF ([Date]=[MD]) Then 1

          ELSEIF ([Date]=[LW]) Then 1

          Else 0

          END

          )

           

          I have attached a template of what I was referring to:

           

          Kind Regards,
          Andrew

          • 2. Re: Compare same day previous week
            Rommel John Gammand

            Hi Andrew,

             

            Thank you for your prompt response. Unfortunately, I can't figure out how to build filter with a formula of maxdate([date]) and also don't know how and where to insert DateAdd Calculation (DATEADD('day',-7,max([Date]))). This is my first time to use formulas in Tableau. Appreciate if you can share instruction to do this.

             

            Here is what I did so far:

             

             

            Thanks,

            Rommel

            • 3. Re: Compare same day previous week
              Andrew Bickert

              Hi Rommel,

               

              You can right in the white area below Measures and click "Create Calculated Field". This is where you will put in the formula for MaxDate and LastWeek (Screen shots below). For the date filter you do, you will need to convert it to a Discrete once it is in the filter pane. An easy way to do this is when you drag Date up to the filter, click on Range of Dates, and click Okay on the next screen. This will put the date (in green indicating a 'continuous' instead of a 'discrete') in the filter area. Right click on the Green Date and choose 'Discrete' instead of 'Continuous'. This will give you the option to choose individual dates. Were you able to open up the TWBX file I had attached?

               

              Kind Regards,
              Andrew

              • 4. Re: Compare same day previous week
                Rommel John Gammand

                Thank you so much Andrew. I figured it out now after opening TWBX file which I didn't notice earlier. Last question, how do i remove columns for Last Week (1/8/2018) data and just show columns for Max Date. In your TWBX file, it shows both but in your screenshot I only see 1/15/2018. I tried to exclude 1/8/2018 but values changed in 1/15/2018.

                 

                 

                Regards,

                Rommel

                1 of 1 people found this helpful
                • 5. Re: Compare same day previous week
                  Andrew Bickert

                  Sorry, I attached the file before I added that filter in and then added the screenshot. I attached the twbx file with the added filter but this is how you would do it.

                   

                  Create a new Calculated field called Last with the formula 'Last()'

                  Drag up to filter area, you will get a Filter of range of values from 0-3. Click OK

                  Right click on Last Filter and go to Compute Using>Table(across)

                  On the popup range values choose range of 0-0

                   

                  If this answers the question please mark as answered so it closes the thread.

                   

                  Kind Regards,

                  Andrew

                   

                  1 of 1 people found this helpful
                  • 6. Re: Compare same day previous week
                    Rommel John Gammand

                    Andrew, you're very helpful. Thank you so much. It's marked as answered now.