7 Replies Latest reply on Dec 4, 2017 5:24 PM by Joshua Preston

    How to get Year (or Month) Difference with values in one column

    Joshua Preston

      Hello -

       

      I have years in a single column in Excel workbook and have tried to get these values with a LOD expression, to count the years (also months) difference b/w two markets. Is there a way to do this in Tableau?

       

      attached 10.4 twbx. thx.

       

      YEAR DIFF CURRENTLY PULLED FROM EXCEL  - I would like to generate those values with Tableau calc.

       

      EXCEL STRUCTURE

        • 1. Re: How to get Year (or Month) Difference with values in one column
          Joe Oppelt

          I'm looking at this.

          1 of 1 people found this helpful
          • 2. Re: How to get Year (or Month) Difference with values in one column
            Joe Oppelt

            See attached.

             

            Sheet 2 is your original.


            Sheet 3 is a copy of sheet 2.  I added [Market] to the sheet.  that's how you tell Tableau to differentiate one year from another.

             

            Sheet 4 is a copy of sheet 3.  I moved [Market] to the detail shelf so that it's still on the sheet for tableau to use.  Then I created a table calc to compare current year with prior year.  (See [year diff].  LOOKUP tells tableau to look for a given field in a given index location.  I used -1 for the offset.)  Right click on [year diff] on the text shelf and select "edit table calc".  Here I told Tableau what dimensional order to walk through the table so that it gets the correct "-1".  Notice that I told it to Restart Every [Restaurant].  You can see I'm getting the correct values now, though it's not displayed in a way you'd want to give to your users.

             

            Sheet 5 is a copy of Sheet 4.  I changed [year diff] from the Text shelf to the Data shelf.  And then I changed it from "continuous" (which previously was  a green pill) to "Discrete" which now make it a blue pill.  Then I dragged it to the ROWS shelf.  At this point we're getting a value for every market (because those combinations independently exist in the table, and different markets get different values, obviously.)  Note that we get a NULL for every restaurant.  That's because the first value (even when there are multiple values) have no "-1" offset for the first value, due to the "Restart every" setting.  (If we turned that off, then the first market for Burger King would get compared to the last market for Arbys, which you don't want.)  But we're inching to the solution!

             

            Sheet 6 is a copy of Sheet 5.  Here I created another table calc: [year diff (copy)].  Look at that.  I'm finding the max [year diff], and by virtue of the table calc settings, I will find the max for each [Restaurant].  I set the table  calc settings like the [year diff] calc.  Made it discrete, and inserted on the sheet in place of [year diff].  Now we get only one value per [Restaurant].

             

            Your data source is set up correctly.  You have dimensional columns that let you analyze the data.  But it takes table calcs to look forward/backward, and sometimes table calcs can be complicated creatures.


            See attached.

            1 of 1 people found this helpful
            • 3. Re: How to get Year (or Month) Difference with values in one column
              Joshua Preston

              Joe - Thank you for the step-by-step instructions to do this. It is extremely helpful and informative! The calc provided the solution I was looking for and taught me more about calc fields along the way. this was a great instructional-style solution. thanks again.

              • 4. Re: How to get Year (or Month) Difference with values in one column
                Joshua Preston

                Hey Joe - I'm trying to apply your method to another slightly different data set, but only get Null values for the datediff calc results. (There's no "Market" equivalent dimension in this data set, but that might only be part of the issue). So obviously from the data, you can see that the date difference is only 1 b/w each year. Can you tell me what needs to change to make this work?

                 

                Also, what I'm really after is the percent change in use by social app, from oldest to newest. Any ideas? thanks in advance. workbook attached.

                 

                • 5. Re: How to get Year (or Month) Difference with values in one column
                  Joshua Preston

                  I figured out the percent change part:)

                  { FIXED [App]: MAX([% of Teens])} -

                  { FIXED [App]: MIN([% of Teens])}

                  • 6. Re: How to get Year (or Month) Difference with values in one column
                    Joe Oppelt

                    See Sheet 4 in the attached.

                     

                    I created a calc called [index] and added it to the sheet.  When I am playing with table calcs I find it helpful to make this calc and add it to the sheet to make sure I am walking the table the way I want it to be walked.  At first I thought maybe the addressing settings were wrong, so this helps me see that in fact we ARE walking this the way we want.  (Restart every [App].)

                     

                    So what could be wrong here?  Well, it turns out that now the data has multiple values of [Survey Date] per year, and the ATTR() function says to get one exact value, and we're making tableau lose its mind trying to do that.  (See Sheet 5 to see the date values.)  You can simulate what tableau is doing in the calc when we do ATTR() by right clicking on the [Survey Date Year] pill in the ROWS shelf and selecting "Attribute" instead of "Exact Date" like I currently have.  See the asterisks?  That's what tableau is trying to use in the [dateDiff] calc, and the results essentially say, "Good luck, buddy.  You get NOTHING from me!" 

                     

                    Given that we have multiple date values, I changed the calc to do MAX(0 instead of ATTR() so that one valid value gets pumped into the calc.  (I could have used MIN() as well there.)

                     

                    One other change.  I reversed the order of the two date arguments in the DATEDIFF function.  Otherwise I was getting -1 instead of 1 for my results.

                    1 of 1 people found this helpful
                    • 7. Re: How to get Year (or Month) Difference with values in one column
                      Joshua Preston

                      Hey Joe - thanks for the breakdown and taking time to explain the results. Sorry so slow to respond. I as trying to wrap my head more around LODs in general.

                      I'll be using this so I can get a better grasp of the concepts. thanks again. Josh