13 Replies Latest reply on Jan 12, 2018 1:21 AM by Simon Runc

    Calculation with dates. Is that even possible?

    Lisa Hoyer

      I have only recently started working with Tableau. Before that I have been a very happy Tibco Spotfire user. My current problem would have been easily solved with a few calculated columns there, however the way calculated values are dynamic in Tableau, according to whatever is dragged into the chart is overwhelming. So I would be very greatful for any solution ideas and approaches. I fear it might not even be possible.

       

      My data contains Ticket-IDs (e.g. from a Support-Hotline). The Status ([Status]) of all existing tickets is checked once a day ([Status_at]) and each ticket has a timestamp of creation ([Created_at]) and if existing a timestamp for when it has been closed ([Closed_at]).

      Now I would like to identify how many tickets have been opened or closed since my last Status Checkup. The solution would be in column F and G.

      Data.PNG

      My target is to then use these columns and created a cumulated chart that looks something like this and shows the sum of created and solved tickets over time.

      Goal-Viz.PNG

       

      I tried creating a calculated value that gives me the previous time stamp of the [Status_at] like this:

      LOOKUP(ATTR([Status_at]),-1)

       

      and then compare the time difference of [Previus_Status_at] and [Status_at] with [Created_at] to [Status_at] (or [Solved_at] to [Status_at]). either in seperate columns or in one big IF loop:

      e.g. DATEDIFF('second', [Closed_at],[Status_at])

       

      And this is where it gets confusing. Depending on how I put the calculations into the chart and depending on how I define wether there are discret or continious, all the answers change. However I haven't found a setting which would calcuate me the column F or G and if I did, I wouldn't know how to put them into a new chart? It seems like a very simple usecase, but I just can't figure it out. Thank you in advance for any ideas!

        • 1. Re: Calculation with dates. Is that even possible?
          Simon Runc

          hi Lisa,

           

          So I've not used Spotfire, but sounds like Tableau thinks a little differently...however what you want is very possible!

           

          I think of Tableau as a SQL database, where the SQL queries are generated via Drag and Drop (not writing SQL), and the results are returned as render instructions (rather than tables). So, for example, if you bring ticket-id (as COUNTD) onto the canvas, and also bring in the date (to make a line chart) it's creating the query

           

          SELECT DATETRUNC('day', [Status As]), COUNT(DISTINCT(Ticket-ID))

          GROUP BY DATETRUNC('day', [Status As])

           

          So any dimensions you add to the rows/columns shelf (or the colour tile, size tile, detail tile...etc,) adds to the Group By. This is why, for aggregated calculations (SUM/AVG/MIN...), the "results" of the calculation are dependent on what is in the canvas (know as the VizLoD, or Viz Level of Detail). I've written the following Quora answer on how to think about the different calculations in Tableau, which you might find useful

          Answer - Quora (as you'll see there are some calculation types which are "off canvas", as I call them)

           

          So onto you actual question...

          There are a few ways (as always with Tableau) we could solve this. I've done it 2 ways.

           

          First is to create a single dimension for your Solved/Closed field (which we can then use to colour the lines)

          [Solition Closed/Created Dim]

          IF NOT(ISNULL([Solution Closed])) THEN 'Solution Closed'

          ELSEIF NOT(ISNULL([Solution Created])) THEN 'Solution Created'

          END

           

          I then use a COUNTD of Ticket-ID, with [Status At] trunced to day level on the Columns. I then bring in my dimension to the colour tile (this is adding an extra GROUP BY so we get 2 lines). I then , using the Tableau Quick Table Calculation, set this to be a Running Sum

           

          Now the problem here is that we don't get any marks until the data starts for each dimension.

           

          So for solution 2 I've split the Ticket-ID into 2 fields (one for Solution and one for Closed) like so (NULLs don't get counted in COUNTD)

          [Ticket-ID: Closed]

          IIF(NOT(ISNULL([Solution Closed])),[Ticket-ID],NULL)

           

          and

           

          [Ticket-ID: Created]

          IIF(NOT(ISNULL([Solution Created])),[Ticket-ID],NULL)

           

          I then bring both these fields in, setting the aggregation to COUNTD, and then to Running Sum and then I dual axis (and sync axis) to get the final chart.

           

          Hope that helps, and makes some sense.

          1 of 1 people found this helpful
          • 2. Re: Calculation with dates. Is that even possible?
            Lisa Hoyer

            Hi Simon,

            thank you for your reply. The intro is really helpful and I feel like I understand the way Tableau works a bit better now!

             

            For my problem, it seems as if I haven't explained it correctly. The columns F and G would not be part of my original data set. I would only have the columns A to E (incl.). My goal is to calculate what is in column F and G and then visualize it. (It would also be fine to calculate F and G in one single column, whatever works best).

             

            Do you have any ideas on how I could approach this.

            • 3. Re: Calculation with dates. Is that even possible?
              Simon Runc

              Glad it was of help...

               

              so we can do these calculations as Row Level (i.e. Off Canvas) calculations very similar to the way you'd do it in Excel

               

              [Solution_Created]

              [Status] = 'B'

               

              [Solution_Closed]

              [Status] = 'D' AND NOT(ISNULL([Closed_at]))

               

              I'm not sure of your Solution_Closed needs both test, but thought I'd include it so you can see how you can include multiple tests.

               

              Once you add these, you can just use these fields where I've used the ones pre-calculated in Excel.

              1 of 1 people found this helpful
              • 4. Re: Calculation with dates. Is that even possible?
                Lisa Hoyer

                Hi Simon,

                 

                again, thank you for all the effort you put into helping me with my problem.

                 

                Unfortuntley I think, there is no easy way to cheat myself around calculating with time stamps and time duration.

                Because Tickets can remain in Status B without beeing newly created at the status call. And other tickets can remain in the overview as "D", for several status calls and then disappear.

                So all I see possible is to compare the created time stamp and closed timestamp for each ticket with the previous and current status call time stamp (through use of time spans).

                And that is what I fear is not really possible in Tableau?

                • 5. Re: Calculation with dates. Is that even possible?
                  Simon Runc

                  hi Lisa,

                   

                  So yes we can probably do this in Tableau, although is likey to get a bit complicated. As Tableau thinks like a database (set theory) & not an Excel (Cell based) moving up and down "cells" can be tricky (especially if you want these computed "off canvas"...and then used at a different level, to that in the data, in the Viz, as per your example). However we can usually force this with some FIXED LoD expressions.

                   

                  If you let me know the rule(s) for those 2 status columns, I'm happy to have a go!

                  1 of 1 people found this helpful
                  • 6. Re: Calculation with dates. Is that even possible?
                    Lisa Hoyer

                    Hi Simon,

                     

                    Here is my explanation of these two Status columns. I hope that is what you mean by rules.

                     

                    The tickets created are cards on a Kanban-Board. So they can have the [status] B-Backlog, N-Next, I-In work, W- Waiting and D-Done. Newly generated tickets usually start in the Backlog but remain there until someone takes care of them. That can happen fast, so they another status than B when the status call is made, or they can remain in B for several status calls, but should still be counted only as new in the first status call after its creation. (Tickets can go back and forth withou any order in these [Status] types)

                     

                    The [status_at] is a manual extraction of the full list of tickets ([Ticket-ID]) at random times of the week. It could happen twice daily and the next time two weeks later. Every behaviour of the ticktes in between status calls is not displayed and therefor the status call shows only a control sample.

                     

                    Tickets that are closed get the status D - Done but can remain part of the sample until they are actually archived manually. Therefore thy should only be counted once at the first status call after their [closed_at]-Closing-Date has been set.

                     

                    In Spotfire (whch works more like a spreadsheet) I would create at an additional Date-column to display the previous status call date to each [Status_at] and then compare if the

                    DATEDIFF('second', [Created_at], [Status_at) < DATEDIFF('second', [Previous_Status_at], [Status_at])

                    and then set it to "Created". (Same for [Closed_at])

                     

                    Does that give you a better understanding of my scenario?

                    • 7. Re: Calculation with dates. Is that even possible?
                      Simon Runc

                      Hi Lisa,

                       

                      So just to check my understanding...I've dusted off my old Excel skills (not used Excel for a while!) to try and get the logic

                       

                      First I assume that in your final formula the [Previous Status at] is just (for each Ticket) the previous status?

                       

                      So in the attached I've broken down each part of the calculation. The problem I seem to have is that the first [status at] for each ticket doesn't have a [Previous Status at]? and How does the Status field fit into this?

                       

                      Just to show we can (although it's tricky!) can do the same thing in Tableau. btw although we can do it in Table form, due to the way Tableau works we'd need to have every row in the data for this to calculate properly, which then creates problems when you want to show a chart at a different level (eg. we need to have all rows in the Viz for this to calculate, but for your chart you only want a count of things by date)

                       

                       

                      I wondering if rather than moving up and down cells (a la Excel/Spotfire) actually we could get the same result by just bringing back the first occurrence of any change in status? (which we could do "off canvas" with an LoD)

                       

                      If you can add in the logic to my Excel for the Solution Created (I've only looked at this one, so we can get the logic and then apply something similar to Solution Closed), then I can try and turn this into a Tableau version.

                      2 of 2 people found this helpful
                      • 8. Re: Calculation with dates. Is that even possible?
                        Lisa Hoyer

                        Hi Simon,

                         

                        I noticed a Copy-Paste-Mistake I made in the original data. I accidentally had two different [Created_at] Dates for Ticket 5 and 6. I changed that (yellow) and also changed your "Previous Status At" column, which had an error, because the [Status_at] wasn't in order (orange).

                         

                        Screenshot.PNG

                        First I assume that in your final formula the [Previous Status at] is just (for each Ticket) the previous status?

                        It would be the previous status over all status calls. If it is for each ticket, then we would never get a TRUE for the Column "Solution_created (Calculated)" in your spreadsheet, because the time it is created never has a previous status call per ticket.

                         

                        So in the attached I've broken down each part of the calculation. The problem I seem to have is that the first [status at] for each ticket doesn't have a [Previous Status at]? and How does the Status field fit into this?

                        [Status] is actually not relevant for this approach.

                        Just to show we can (although it's tricky!) can do the same thing in Tableau. btw although we can do it in Table form, due to the way Tableau works we'd need to have every row in the data for this to calculate properly, which then creates problems when you want to show a chart at a different level (eg. we need to have all rows in the Viz for this to calculate, but for your chart you only want a count of things by date)

                        Thats what I had trouble with as well. Having a simple chart after a complicated calculation (Honestly, I can't even create the table view that you have in your screenshot. As soon as I try to add "Created_at" to the table it gives me an asterisk "*" on each line)

                         

                        I wondering if rather than moving up and down cells (a la Excel/Spotfire) actually we could get the same result by just bringing back the first occurrence of any change in status? (which we could do "off canvas" with an LoD)

                        Yes I assume, we could mark a Ticket as "Created", the first time its "Creation Date" appears and mark it as closed, the first time its "Closed_at" status appears. That would solve this problem, but I would still be uncomfortable using Tableau for many of my other time-series-data use cases (which I unfortunately have a lot of).

                        • 9. Re: Calculation with dates. Is that even possible?
                          Simon Runc

                          So here is a version which matches (for the data you've posted) the Solution Created field. I've done this as an FIXED LOD, which means (in my terminology) it is an "off canvas" calculation, so the level of detail in the canvas doesn't affect how this calculation is run. I've spolit it into 2 parts to help the explanation, but you could nest into 1 in your final version (if you wish)

                           

                          So first I need to get the 1st [Status At] for each Ticket/Status

                          [Solution Created - 1st Status At per Ticket/Status]

                          {FIXED [Ticket-ID], [Status]: MIN([Status at])}

                           

                          Think of this like a MINIFS in Excel (not that I think it has that that function...but pretend there is a MINIFS, which works like SUMIFS)...where we are FIXing the level at which the calculation is run (which is why it can be run correctly regardless of the VizLoD)

                           

                          I then use this field to create the True/False

                          [Solution Created - Tableau]

                          [Status at] = [Solution Created - 1st Status At per Ticket/Status]

                          AND

                          ISNULL([Closed at])

                          AND

                          [Status]='B'

                           

                          btw I know you said that Status was irrelevant, but I had to include the Status = 'B' to get the calculation to work as per your version? If you can let me know how I differentiate between status, without the status field, I can hopefully add this rule in.

                           

                          There is also a tab called "Rules" which shows how I built the table before (off the old data) just in case you are interested in the "On Canvas" version!

                          2 of 2 people found this helpful
                          • 10. Re: Calculation with dates. Is that even possible?
                            Lisa Hoyer

                            Hi Simon,

                             

                            thank you so much for you help. I think I can figure the rest out from here. I guess the future will tell me if I have completly understood what I am doing

                            • 11. Re: Calculation with dates. Is that even possible?
                              Simon Runc

                              Cool...glad it did the trick.

                               

                              Happy for you to ping me on any other questions you post...as it will likely take a few examples for the understanding to fully emerge! (I find moving from Cell based thinking to Set thinking is one of the biggest challenges people face in Tableau, but once you think like a database you'll never look back!). I'd also add that Tableau isn't a data prep tool (although Maestro looks like it will fix that), and although most of the time we can use LoDs to do this kind of thing, there are other times where a bit of data prep (eg. getting the database view to generate ranks) is quicker/easier, in the long run (an hour spent writing a bit of Python/SQL, or using something like Alteryx, is got back 10 fold with how easy it is to create Vizes in Tableau!).

                              1 of 1 people found this helpful
                              • 12. Re: Calculation with dates. Is that even possible?
                                Lisa Hoyer

                                While working through your reply and trying to complete it, I came up with a simple solution for this problem. I guess it was just too obvious ...

                                 

                                On the original data, i just create two additional "Off-canvas"-columns for a time difference between the [Status_at] and either the date of closure or creation:

                                 

                                [TD C]

                                DATEDIFF('minute', [Created_at], [Status_at])

                                 

                                [TD R]

                                DATEDIFF('minute', [Closed_at], [Status_at])

                                 

                                And now I just check if each time difference is the smallest (but not negative for [TD R]) of all time differences for each ticket.

                                [C vs R]

                                IF [TD C]={FIXED [Ticket-ID]: MIN([TD C])}

                                    THEN "C"

                                    ELSEIF [TD R]={FIXED [Ticket-ID]: MIN([TD R])} AND [TD R] > 0

                                        THEN "R"

                                END

                                 

                                (And for visualisation purpose I aslo create a counter for the last [C vs R] column)

                                [Count C vs R]

                                IIF(ISNULL([C vs. R])=FALSE, 1, NULL)

                                • 13. Re: Calculation with dates. Is that even possible?
                                  Simon Runc

                                  very good...looks like your getting the hang of things! (FIXED LoD are one of the more advanced, and most useful, calculation types in Tableau...so good work!)