1 2 Previous Next 17 Replies Latest reply on Dec 15, 2014 9:30 AM by Jonathan Drummey

    Comparing  value of a field in two different dates

    Pankaj Chopra

          I have a Status field which changes every week from Red to Yellow or Green or from Green to Red for a given project id. Basically it could downgrade or upgrade every week.

       

      I have data for every week on Sunday. As an example like below

       

      Date field                                          Status       Project id

      11/9/2014                                       Red           567

      11/9/2014 (most current week)            Green           353

      11/2/2014                                          Yellow        534

      11/2/2014                                          Yellow          353

      10/26/2014                                           Green        567

       

      I need to have a view in which to generate a filter having values - Upgrade , Downgrade , All

       

      and also have two date filters ( user selectable) - the base week ( most current week) and the comparison week ( list of other weeks)

       

      So for example when a user selects for the above example 11/9/2014 and  comparison week 10/26/2104 and click downgrade

      it should show Project id 567 since it downgraded from green to red status

       

      If he clicks base week as 11/9/2104 and comparison week as 11/2/2014 and clicks upgrade it should show project id 353 since the status between those weeks for project id 353 upgraded from yellow to green

       

      The base week is always the current week.

       

      Can anyone help me accomplishing this?

        • 1. Re: Comparing  value of a field in two different dates
          Tableau kumar

          Attach workbook, it will help us to Understand.

           

          Best Regards

          Laxman Kumar

          • 2. Re: Comparing  value of a field in two different dates
            Pankaj Chopra

            Hi Laxman

             

            I attached a workbook to explain better. Thanks

            • 3. Re: Comparing  value of a field in two different dates
              Tableau kumar

              Hi we can create calculation like below

               

              If [Status this week]= "Red" and [Status last week]="Yellow" then "Downgrade"

              elseif [Status this week]= "Green" and [Status last week]="Yellow" then "Upgrade"

              elseif [Status this week]= "Green" and [Status last week]="Red" then "Upgrade"

              end

               

              Before above calculation,

              convert your dated into Weeks & define Current Week & Last Week.

              datepart("Week", today()) ---- Current Week

              datepart("Week", today()) -1   ---- Previous Week

               

               

              Best Regards

              Laxman Kumar

              • 4. Re: Comparing  value of a field in two different dates
                Pankaj Chopra

                Hi Laxman

                 

                Thanks for your response but how do I create the [Status this Week] and [Status Last Week] columns. I don't have that in my data source.

                 

                Thanks

                • 5. Re: Comparing  value of a field in two different dates
                  Suhrid Ghosh

                  Hoping to get an answer for this from experts !!!! Mean while trying from my side  ...

                  • 6. Re: Comparing  value of a field in two different dates
                    Tableau kumar

                    I hope it will work for you.

                     

                     

                    if    datepart("Week",[startdate]) = datepart("Week", today()) then [Status] end

                     

                    ifdatepart("Week",[enddate]) = datepart("Week", today()) then [Status] end
                    • 7. Re: Comparing  value of a field in two different dates
                      Suhrid Ghosh

                      Hi,

                       

                      Have worked through the solution ... Hope it Helps ...

                       

                      Thanks,

                      Suhrid Ghosh

                      2 of 2 people found this helpful
                      • 8. Re: Comparing  value of a field in two different dates
                        Pankaj Chopra

                        Hi Suhrid

                         

                        Do I need to have the latest Tableau version to open the above file?

                        • 9. Re: Comparing  value of a field in two different dates
                          Suhrid Ghosh

                          Unfortunately Yes ... Since i am on 8.2.2 ...

                          • 10. Re: Comparing  value of a field in two different dates
                            Pankaj Chopra

                            Hi Suhrid

                             

                            I looked at the solution and it works pretty good but in the Dashboard view when I click on Upgrade/Downgrade it should only show the corresponding rows. Currently it shows all rows. Is there a way we can filter out the other rows? I tried using actions but was not successful

                             

                            Thanks

                            • 12. Re: Comparing  value of a field in two different dates
                              Jonathan Drummey

                              There are multiple ways to tackle this, each with different tradeoffs. The first route I chose makes use of a Top 1 Set to identify the latest week, a self-data blend to create a "dynamic" parameter, and table calculations to generate the results in a single worksheet. This avoids the problems with parameters that have to be updated every time the data changes, and will still show the latest available data no matter how late that data might arrive.

                               

                              Here's a brief overview of how I built it:

                               

                              1) Created a view with Project ID & Date field on Rows.

                               

                              2) In the primary data source, created a Top 1 Set called "Is Latest Date" on the Date field dimension using MAX(Date). The IN/OUT of the Set returns True for the latest date across the entire data set.

                               

                              3) Created a "Latest Status (non-optimized)" calc with the formula IF [Is Latest Date] THEN [Status] END. This returns a dimension. Put that on Rows as an ATTR(Latest Status (non-optimized).

                               

                              4) Duplicated the data connection.

                               

                              5) Brought ATTR(Date field) from the secondary (duplicate) source and put that on Rows. This returns the same value as Date field from the primary, the next two steps change that.

                               

                              6) In the secondary source, turn *off* the blend on Date field. You'll now see * for the ATTR(Date field).

                               

                              7) Right click+drag Date field from the secondary onto the Filters Shelf, choose Individual Dates and Times, and pick a single date. You'll now see the * for ATTR(Date field) on Rows change to the chosen date.

                               

                              8) Changed that filter into a Quick Filter and edited it to be a single value dropdown with no All value. We'll still see the Null value no matter what, that's a Tableau behavior that can't be changed.

                               

                              9) Created a "Selected Status (non-optimized calc)" using the formula

                              IF ATTR([Date field]) == ATTR([Sheet2 (Sample worksheet) (copy)].[Date field]) THEN

                                      MIN([Status])

                              END

                               

                              (Steps 4-9 draw from Creating a Dynamic “Parameter” with a Tableau Data Blend | Drawing with Numbers).

                               

                              8) Put that on Rows, you'll now have a view that looks like this:

                               

                              2014-11-18 10_37_14-Tableau - Book1.png

                               

                               

                              10) Now that we have the Latest & Selected statuses, we need to be able to compare them. The view requires the Date field to be a dimension for the calc in step 7 to work, so we can't just use a regular aggregate calc to compare the two. In order to do a comparison across the marks, we can use table calculations to "return" the values of the Latest & Selected Statuses to a given mark (address aka row in the partition), and with everything we need in the same place do the Downgrade/Upgrade computation.

                               

                              Here's the Latest Status calc:

                              IF LAST()==0 THEN

                                  WINDOW_MAX(ATTR(IF [Is Latest Date] THEN [Status] END))

                              END

                               

                              And the Selected Status calc:

                               

                              IF LAST()==0 THEN

                                  WINDOW_MAX(IF ATTR([Date field]) == ATTR([Sheet2 (Sample worksheet) (copy)].[Date field]) THEN

                                      MIN([Status])

                                  END)

                              END

                               

                              Put both of the calcs on Rows and set the Compute Using of the calcs is set to the Date field. The WINDOW_MAX() is going to return the non-Null value of the given Status to every address in the partition, then the IF LAST()==0 says to only do that for the last (latest) address in the partition. With the Compute Using on the Date field, the calcs are partitioned on the Project id so only return a result for the latest date in each partition.

                               

                              11. Now to create the Downgrade/Upgrade calc. This one includes all the edge conditions, so far as I can tell:

                               

                              //only test for last address in partition (latest date)

                              IF LAST()==0 THEN

                                  //checking in case the user selected the latest date

                                  IF ATTR([Date field]) == ATTR([Sheet2 (Sample worksheet) (copy)].[Date field]) THEN

                                      "Selected date is latest"

                                  ELSE

                                      IF [Latest Status] == "Red" AND ([Selected Status] == "Yellow" OR [Selected Status] == "Green") THEN

                                          "Downgrade"

                                      ELSEIF [Latest Status] == "Yellow" AND [Selected Status] == "Green" THEN

                                          "Downgrade"

                                      ELSEIF [Latest Status] == "Green" AND ([Selected Status] == "Yellow" OR [Selected Status] == "Red") THEN

                                          "Upgrade"

                                      ELSEIF [Latest Status] == "Yellow" AND [Selected Status] == "Red" THEN

                                          "Upgrade"

                                      END

                                  END

                              END

                               

                              In the view this also has a Compute Using on the Date field:

                              2014-11-18 10_46_57-Tableau - Book1.png

                               

                              12. Now to create the final worksheet. Duplicate the workout view, drag the Date field from Rows to the Level of Detail Shelf, move all other unneeded fields from Rows out of the view, and drag the Downgrade/Upgrade field to the Filters Shelf and filter for the desired value:

                               

                               

                              2014-11-18 10_50_54-Tableau - Book1.png

                               

                               

                              Ok, now that I'm done typing that out, I just came up with another way to do this and that's to flip the blend around, so the secondary source is only returning the latest date for the project, and the primary source is looking at all the dates. This will get rid of the Null value in the Date field filter, and mostly get rid of the Null value in the Downgrade/Upgrade filter:

                               

                               

                              2014-11-18 10_58_31-Tableau - Book1.png

                               

                              This uses the blended source to get the Latest week's status, and since the blend is only returning a single value we don't need the Date field in the view. This simplifies the rest of the calcs and we can compute the Downgrade/Upgrade as a regular aggregate. However, Tableau won't let us put discrete aggregate measures on the Filters shelf, so I wrapped that in a LOOKUP() formula to create a table calc filter.

                               

                              I set both of these up in the attached workbook.

                               

                              If you wanted the user to dynamically choose both dates, there's yet another route using a multiple select filter that could be used.

                               

                              Jonathan

                              1 of 1 people found this helpful
                              • 13. Re: Comparing  value of a field in two different dates
                                Pankaj Chopra

                                Thanks Jonathan

                                 

                                Great explanation and work. Thanks a bunch. I was stress testing it and figured out if a project id is

                                 

                                present in a latest date and has a status ( lets say red) and not present in an earlier date - in this case when you select the earlier date for comparing the statuses ( I know I would be comparing Null to  Red ) that project does not shows up. That project could be a new entry in an actual data for the latest date. Basically irrespective of the comparison date, I would like all project ids to show up.

                                 

                                Is there any way to resolve this? Would I have to do joins between the two sources instead of blending?

                                • 14. Re: Comparing  value of a field in two different dates
                                  Jonathan Drummey

                                  In the data blending solutions I offered, when you select an earlier date that doesn't exist for a given project as a record in the data source, what happens is you select the filter, then in the data blending process Tableau only retains tuples (combinations of project and date) that exist in both data sources.

                                   

                                  If you always want all project ids to show up *and* show current data no matter what is filtered for, the workarounds that I can think of are:

                                   

                                  1) Use a parameter for selecting the date instead of a data source. This would require updating the parameter every week. Andy Kriebel demo'ed a hack for downloading, updating parameters, and re-uploading a workbook at #data14.

                                   

                                  2) Use a custom query or view to pad out your data so there's a record for every project/date combination in the data for the date range that you are using, this would likely be using some sort of cross-product join, and then use one of the data blending solutions I posted. Without knowing your data volumes  I can't say whether that would work, however it would most precisely match the experience for users that you want.

                                   

                                  3) Use a custom query or view to left-join this week's data with every prior week that exists, so if there are no prior weeks then the prior week fields would all have Null values. Then all the calculations could be record-level calculations, and you could potentially use Tableau's "Show Empty Rows/Columns" feature to retain the display, or you'd have to train your users to use a multi-select filter and keep Null selected while they changed other options.

                                   

                                  Jonathan

                                  1 2 Previous Next