9 Replies Latest reply on May 3, 2017 12:18 PM by Andrew goecke

    How do I change a  table calculation on date field into a dimension?

    Andrew goecke

      Hello!

       

      I have a few issues, I have one date column that I need to run a difference calculation on. Below is a sample data set, I need to run a table calculation that gives me the difference of days between each date for each account number. I have done this in my attached workbook with 1 issue, my table calculation returns the difference that I need, but it also returns the difference between account numbers, which I don't want. Now I know what you are thinking, change the table calculation to run on the pane level, but this causes an error with my difference formula. My final issue is that I want to save the returned values of my table calculation as its own measure, so that I can use it for further calculations, is this possible?

       

      Here is the resource I used to get the date difference within 1 single dimension to work: http://kb.tableau.com/articles/howto/calculate-difference-in-dates-with-one-date-field

       

      Here are the calculated fields I created to get the difference to work: reference date=DATE("1/1/1900"), Difference in dates=DATEDIFF("day", MAX([Reference Date]), MAX([Date]))

       

       

      sample data:

       

      Workbook status right now, cab I get the table calculation to save as its own measure?:

        • 1. Re: How do I change a  table calculation on date field into a dimension?
          Joshua Milligan

          Andrew,

           

          As much as I love table calculations, when I hear you say that you want to be able to use the results of your calculation as a dimension, I immediately think that a table calculation is not going to be the direction you want to go.  Table calculations are a special type of calculation that work on the aggregate data that has been returned to Tableau based on the query generated by the placement of your other dimensions and measures in the view.

           

          So, instead, how about a FIXED Level of Detail calculation?  The benefit of this approach is that a FIXED LOD calc returns a row-level result that can be used as a dimension.  In this case, I'll actually create a couple of calculations.

           

          1. First Date for Account  this is the FIXED LOD that will return the minimum date per account.  The code is {FIXED [Account Number] : MIN([Date])} and you can see that it does indeed give you the first date:

           

          Then, you can use that calculation in a DATEDIFF calc to find the difference in days between the First Date and the Date

           

          2. Date Difference with the code DATEDIFF('day', [First Date for Account], [Date]).

           

          And you don't have to worry about how to compute it -- it just works at the row level.  And you can make Date Difference a dimension if you want and use it like you would any other dimension - to slice the data, in other calcs, etc...

           

          Hope that helps!

          Joshua

          • 2. Re: How do I change a  table calculation on date field into a dimension?
            Andrew goecke

            Joshua,

             

            Thank you so much for your clear and concise help. I am very close but not quite there yet using your answer. I need the calculation to run the difference between each date, starting with the first date. Using your last screenshot to give more clarity, I need the last row of values to look like this:

            0

            2

            4

            0

            2

            5

             

            Right now you have each date subtracting from the first value, but I need the calculation to run down the table.

            • 3. Re: How do I change a  table calculation on date field into a dimension?
              Joshua Milligan

              Andrew,

               

              Oh! I missed that.  Okay, so that actually complicates the LoD approach because, while LODs are great for finding mins/maxes, first values, etc... they are not very good at moving along a table.

              So, that brings me back to thinking along the lines of a table calc.  The concerns are 1) it causes "an error" and 2) how to use it in other calculations.

               

              The first thing I'm going to suggest is to re-write the calculation a bit using code like this:

               

              IF FIRST() == 0 THEN 0

              ELSE DATEDIFF('day', LOOKUP(MIN([Date]), -1), MIN([Date]))

              END

               

              The basic idea is that if it's the first date (when FIRST() == 0) then we'll have a 0 value otherwise, we'll get the difference in days between the previous date (LOOKUP - 1) and the Date.

               

               

               

              And then, you can compute the calculation along Date (which is equivalent to Pane Down in the view as it is now, but we'll change the view to get around the first concern, so it will need to be computed using Date).

               

              When you use Date or Pane Down, you'll see something like this:

               

              which is Tableau doing something called "data densification"  Basically, the table calculation is causing Tableau to fill in missing values -- in this case all the dates for each account number.  This can be useful sometimes, but is not what you want here.

               

              To disable it, we'll move the Date off the Rows field and into the Detail of the Marks card.  Instead, on Rows, we'll use the Attribute of the the Date (which means it's no longer a dimension and will no longer trigger the data densification).

               

               

              And now, your calculation is working and giving you the values you want.

               

              Hopefully, that gets you what you need!  If not, I'd be happy to take a further look.

               

              Best Regards,

              Joshua

              1 of 1 people found this helpful
              • 4. Re: How do I change a  table calculation on date field into a dimension?
                Andrew goecke

                Hi Joshua,

                 

                Thank you for your help! I am so close and I think we can do this. For some reason, when I select pane down, it returns every day of the month for me, only showing data for dates I have data in for that account number. I do not know if this is because my data set is huge, I really do not know what is going on.

                 

                Thank you,

                 

                Andrew

                • 5. Re: How do I change a  table calculation on date field into a dimension?
                  Andrew goecke

                  Hi Josh,

                   

                  wanted to get you a screenshot of my best attempt yet, what do you think?

                  1 of 1 people found this helpful
                  • 6. Re: How do I change a  table calculation on date field into a dimension?
                    Joshua Milligan

                    Andrew,

                     

                    It looks pretty good.  I'm not sure what's going on with your first dates for each call as I would have expected them to be 0.  Otherwise, does everything seem to be working now?

                     

                    -Joshua

                    • 7. Re: How do I change a  table calculation on date field into a dimension?
                      Andrew goecke

                      Everything besides the first number is working yes, but I really want to fix the issue so I can do some conditional formatting on the table without it looking all wonky on the first number.

                       

                      Regards,

                       

                      Andrew

                      • 8. Re: How do I change a  table calculation on date field into a dimension?
                        Joshua Milligan

                        Andrew,

                         

                        What formula are you using for the value?  Is it:

                         

                        IF FIRST() == 0 THEN 0

                        ELSE DATEDIFF('day', LOOKUP(MIN([Date]), -1), MIN([Date]))

                        END

                         

                        If it is, then I would definitely expect the first value to be 0.  Do you happen to have an updated workbook to share?

                         

                        Best Regards,

                        Joshua

                        • 9. Re: How do I change a  table calculation on date field into a dimension?
                          Andrew goecke

                          Josh,

                           

                          You solved my issue and you are the man!! Wow, you are a great at tableau.

                           

                          Thank you!!!!

                           

                          -Andrew