7 Replies Latest reply on Dec 12, 2016 6:57 PM by Tom W

    Display Last Year value in a filtered dataset

    J.R. Wilton

      Hello,

       

          I have been tasked by our leader to do a simple grid but there is a large amount of complexity for it. 

       

      Here is my dataset

       

      Date            Value

      1/1/2015     3

      1/2/2015     4

      1/1/2016     5

      1/2/2016     6

       

      The final output should be

      Date          TY          LY

      1/1/2016     5          3

      1/2/2016     6          4

       

      They want to be able to use a date range quick filter which is causing my problem.  Since it is filtering the dataset to 1/1/2016 to 1/2/2016, I am not getting any of the value for 2015 in the last year table.  How can I filter TY but still see LY value.

       

      Thank you,

        • 1. Re: Display Last Year value in a filtered dataset
          Tom W

          Hi JR,

          You can achieve this with Level of Detail expressions. Basically you'll need to create a calculated field for TY and LY and use an LOD in each. If you'd like further specific help, please upload a Tableau Packaged Workbook including your sample data.

          • 2. Re: Display Last Year value in a filtered dataset
            J.R. Wilton

            Please find attached the workbook as simple as it can get.  Sorry I didnt do any other calculation because I dont know if it is achievable.

             

            The final result would be Date, TY Value, LY Value

            • 3. Re: Display Last Year value in a filtered dataset
              Tom W

              One straight forward way; Create a calculation called Combined Date. This will effectively fake everything as 2016. I.e. 1-1-2015 becomes 1-1-2016.

              Combined Date =

              DATETRUNC('day',

              IF Year([Date])=2016 THEN [Date]

              ELSE dateparse("yyyy-mm-dd","2016-" + str(month([Date])) + "-" + str(day([Date])))

              end

              )

               

              You'll see how this works when you then put CombinedDate alongside Date;

               

              Now, if you drag out Date onto the columns shelf you will be able to get a comparison of the two years;

               

              Setup your quick filter on the CombinedDate dimension, not the original date field.

               

              Another approach builds on what you've already done with the combined field, but gets a bit more specific. Create a calculated field called LY as SUM(IF YEAR([Date])=YEAR(Now())-1 THEN [Value] END)

              Create a calculated field called TY as SUM(IF YEAR([Date])=YEAR(Now()) THEN [Value] END)

               

              Drag measure names onto the columns shelf, then drag Measure Values onto the Text button on the marks card. Double click the measure names pill on the filter shelf and select LY and TY only. Your sheet should look like this;

              • 4. Re: Display Last Year value in a filtered dataset
                J.R. Wilton

                Unfortunately the NOW() will not work in the SUM(IF YEAR([Date])=YEAR(Now())-1 THEN [Value] END).  I have two challenges with this and trying to figure a way around.  The first one is that the data would expand multi year so the NOW() would not work if you are comparing year-2 to year-3.  The other challenge is they want it all day of week match which is Dateadd('day',-364, date).  I tried multiple variation but the Data wont return.  Thank you so much so far for your help.  Hopefully we can solve this remaining hurdle.

                 

                Tableau.jpg

                • 5. Re: Display Last Year value in a filtered dataset
                  Tom W

                  Please upload your latest workbook

                  • 6. Re: Display Last Year value in a filtered dataset
                    J.R. Wilton

                    I have attached my latest and I added 10 day for 2014 in the data as well.  Thank you so much.

                    • 7. Re: Display Last Year value in a filtered dataset
                      Tom W

                      What if you tried this approach instead;

                      • Create a calc called MaxYear as YEAR({MAX(Date)})
                      • Create a calc called YearDiff as [MaxYear]-YEAR([Date])
                      • Create a calc called NewDate as DATEADD('day',(364)*[YearDiff], Date)
                      • Drag NewDate into your report on the rows, you can now apply the quick filter to this to select the '2016' date.
                      • Drag YEAR(Date) onto the columns shelf, you should now have your years across the page.

                       

                      In terms of how this actually works with your data, you should pull 'NewDate' onto the row and also drag Date onto the row so you can see how it's being grouped i.e.