1 2 Previous Next 15 Replies Latest reply on Nov 7, 2017 2:09 PM by Chuy Sanchez

    Calculated Field to Count Items Based on Dates Opened/Closed?

    dustin baker

      Hello...new to Tableau (Desktop) and the Tableau Community; please bear with me

       

      I created a Tableau workbook whose data sources are two different excel spreadsheets; the content of the data is related to IT help tickets.  I have created a dashboard based on this data—which I submit to my supervisor every quarter.  The dashboard is basically a snapshot of some key metrics as of the last day of the quarter and one of the metrics is the number of "open" tickets.  The issue I'm having is that I cannot pull the data on the last day of the quarter...it is usually available to me a week or two after the quarter ends.  Thus, if I just pull over my [Ticket Status] field, and filter by "Open," my number is incorrect because (1) there have been some new tickets opened in between the last day of the quarter and the day I received the data and (2) there have been some tickets closed out in this same time period.  My thought was to create a calculated field that is basically:

       

      Number of tickets with [Ticket Status] = "Open" + Number of tickets with [Date Closed Out] in current quarter - Number of tickets with [Date Initiated] in the current quarter

       

      I'm having trouble doing this and tried to Google a solution and then customize it to my situation—no luck there. I'm also wondering if it is possible to do this with some sort of "Boolean filter" as well.  Any help you can provide will be appreciated.  Thanks in advance.  

       

      Dustin

       

      P.S. my supervisor wouldn't let me attach the workbook because some of the data is "for company use only."

        • 1. Re: Calculated Field to Count Items Based on Dates Opened/Closed?
          Marc-Anthony Di Biase

          Hi Dustin,

           

          You may find this resource helpful for any future queries you have on the forums: Anonymize your Tableau Package Data for Sharing. Attaching a workbook not only makes it easier for someone to help you out but allows you to get a solution that is geared specifically to your data set.

           

          In reference to your problem at hand, it wouldn't be difficult to gather a number of tickets with a given status if your data set has dates attached to each corresponding tickets. Attaching either an anonymized packaged workbook or a sample of your data would allow me to build out the formulas you would need based on your data structure/field names.

          1 of 1 people found this helpful
          • 2. Re: Calculated Field to Count Items Based on Dates Opened/Closed?
            Ben Neville

            I think you can simplify your logic. You just need to dynamically calculate the end of the quarter. Let's say you're operating on a standard 12-month January-December calendar. Your prior quarter close would be the last day of June. To get this, we would use:

            DATEADD('quarter',1,DATETRUNC('quarter', TODAY()))-1

             

            Then we wrap this in some logic to determine open tickets. While you have a flag, we can't trust it for the reasons you mention. I assume that the [Date Closed Out] field is blank if there is no close date associated, so I'd use something like:

            COUNTD(IF [Date Initiated] <= DATEADD('quarter',1,DATETRUNC('quarter', TODAY()))-1 AND (ISNULL([Date Closed Out]) OR ([Date Closed Out] > DATEADD('quarter',1,DATETRUNC('quarter', TODAY()))-1)) THEN [Ticket ID] END)

             

            You could use the above however you like - remove the COUNTD and output a 1 instead of a ticket id, then sum if there's only 1 ticket per row, remove the "COUNTD(IF" from the beginning and the "THEN [Ticket ID] END" from the end and it's a boolean field you can either use in another calculation or as a filter, etc.

            1 of 1 people found this helpful
            • 3. Re: Calculated Field to Count Items Based on Dates Opened/Closed?
              dustin baker

              Thanks Marc...didn't know you could anonymize the data like that.  I'm attaching a simplified workbook with dashboard described above.  Let me know if this is what you were asking for.  Thanks.

              • 4. Re: Calculated Field to Count Items Based on Dates Opened/Closed?
                dustin baker

                Thanks for your reply Ben.  I gave what you suggested a try but the number of open tickets is higher than the real number.  I attached an anonymized workbook above if you care to give it a go yourself.  Thanks for your help.

                • 5. Re: Calculated Field to Count Items Based on Dates Opened/Closed?
                  Marc-Anthony Di Biase

                  Thanks for the workbook Dustin, just so I'm clear on your requirement here, you have a field that brings in the number of tickets, that gets impacted by a Status filter. The issue you have across all statuses, but specifically the open tickets is that if a ticket is opened after the end of the quarter, it appears in this number, making it inaccurate. I would recommend taking a look at Ben Neville's suggestions as if I were to tackle this problem, I would likely use the DATETRUNC functions to trim to the quarter end dates and use this to validate the tickets that are being pulled into the number.

                   

                  If we are still off, please clarify and I'm sure one of us will be able to give you a solution within your anonymized workbook!

                  • 6. Re: Calculated Field to Count Items Based on Dates Opened/Closed?
                    Ben Neville

                    If you're comparing to last quarter, then I should amend my original suggestion and remove the quarter addition (I originally wrote the calculation for computing against current quarter). Try:
                    COUNTD(IF [Date Initiated] <= DATETRUNC('quarter', TODAY())-1 AND (ISNULL([Date Closed Out]) OR ([Date Closed Out] > DATETRUNC('quarter', TODAY())-1)) THEN [Ticket ID] END)

                    • 7. Re: Calculated Field to Count Items Based on Dates Opened/Closed?
                      dustin baker

                      @Ben Neville

                       

                      I'm still getting a number that is too high with your updated formula.  I'm new to Tableau and also to these formulas so I'm trying to de-code your formula to understand where it's going wrong.

                       

                      I think that the formula says:

                       

                      Count an entry if:

                      [Date Initiated] >= last day of last quarter AND [Date Closed Out] is empty OR [Date Closed Out] > the last day of last quarter

                       

                      For a moment I thought I figured out where the formula was not behaving as desired.  It is returning a value of 81 when my manual calculation shows that it should be 60.  There are 3 types of [Ticket Status]: Open, Closed, and Cancelled.  My guess was that it was also counting the "Cancelled" tickets because the [Date Closed Out] field will be NULL just like an "Open" ticket.  However, there are 16 Cancelled tickets and if you subtract those from 81, you are left with 65.  So, I am lost again!!

                      • 8. Re: Calculated Field to Count Items Based on Dates Opened/Closed?
                        Ben Neville

                        In plain English, return the Ticket ID if the date initiated came before the last day of last quarter AND the closed date is either null or came after the last day of last quarter. Then count the unique number of tickets. If it's not returning the number you want, I believe there is something else going on in your data as the logic is sound.

                         

                        My calculation does not account for Cancelled, so you would probably want an IF [Ticket Status] <> "Cancelled" AND in place of the IF which currently begins the calc. But there are 5 tickets that are not being accounted for in the logic. Find what attributes those share and you'll find why they're being unintentionally counted.

                        1 of 1 people found this helpful
                        • 9. Re: Calculated Field to Count Items Based on Dates Opened/Closed?
                          dustin baker

                          Ben,

                           

                          Below is the formula I ended up with.  I couldn't figure out how to replace the first IF statement as you suggested (it gave me a count of 362 when I tried)...I'm guessing there is some "order of operations" that I don't yet understand.  The formula is giving me my desired count but I want to make sure I don't have any unintended consequences by having it arranged like this:

                           

                          COUNTD(IF([DATE  INITIATED]<= DATETRUNC('quarter'',TODAY()-1) AND ISNULL([DATE CLOSED OUT]) AND ([MOC STATUS<> "Cancelled") OR ([DATE CLOSED OUT]> DATETRUNC('quarter',TODAY()-1))) THEN [MOC #] END)

                           

                          Also, I took a closer look at the data and was able to confirm and subtract out the 15 "Cancelled" tickets from the count of 81 I was getting with Ben's updated formula.  I adjusted the formula to exclude "Cancelled" tickets (shown above) and this left me with a count of 66 — 60 "Open" tickets and 6 "Closed" tickets.  I then pulled the [Date Closed Out] fields for each of those 6 "Closed" tickets and noticed that five of them were closed in July '17 and one was closed on 8/1/17.  This checks out because all of those 6 were closed in Q3 (current quarter). 

                           

                          If I want a "monthly" dashboard (i.e., where I want a snapshot of the month's metrics on the last day of the month even though my data is a week into the new month), then can I use this formula?

                           

                          COUNTD(IF([DATE  INITIATED]<= DATETRUNC('month',TODAY()-1) AND ISNULL([DATE CLOSED OUT]) AND ([MOC STATUS<> "Cancelled") OR ([DATE CLOSED OUT]> DATETRUNC('month',TODAY()-1))) THEN [MOC #] END)

                           

                          Secondary questions:

                          (1) Are there any good resources for learning to use these functions to write calculations.  I'm mildly familiar with some of them from Excel but when you add new functions (i.e., Countd) and a bunch of nested functions, I find it difficult to interpret.

                          (2) Is there a good method for formatting a nested formulas like the one above for a newbie like me so I can "decode" it...I'm thinking of the way people who write code use separate lines and indent.  Just curious because I would like to "learn to fish" eventually as opposed to having others "catch fish" for me. Like I said above, I'm assuming there is some information about "order of operations" that I need to learn so I can arrange formulas correctly.

                           

                          Thanks for all the help you guys are providing in walking me through this...it is very appreciated.

                          • 10. Re: Calculated Field to Count Items Based on Dates Opened/Closed?
                            Ben Neville

                            Well, first off, I don't think this is the exact calculation you used because the one you used would have an error due to the mismatch of the single and double quotes in the first DATETRUNC. Further, your logic in the DATETRUNC needs the -1 outside the function, as right now it is grabbing the quarter of yesterday (which is always the same as today's quarter except 4x/year). You need to truncate it to the quarter first (which would return July 1st), then subtract a day, giving you the last day of last quarter (June 30th). This problem also exists in the second DATETRUNC.

                             

                            Even so, looking past these 2 issues, it's a bit strange this works due to the ways the ANDs and ORs are organized. This one's always fuzzy to me until I think on my computer science roots, but based on your lack of containing parentheses around some of these functions, your calculation reads to me like:

                            "If the Initiated Date was before the end of last quarter AND the Date Closed Out is null AND the MOC Status is cancelled OR the Date Closed Out came after the quarter THEN return the MOC #."

                             

                            The issue with this to me is that any MOC which has a closed date after the quarter should be returned. Which means any tickets from this quarter should be returned (again, ignoring the -1 placement). The only others which are returned would be those with an initiated date before the end of last quarter and with a null closed date and with an moc status of cancelled.

                             

                            Does that make sense?

                             

                            Because of the above reasons, I can't really comment one way or the other on your hypothetical monthly calculation, but I will say that once you arrive at the correct calculation for quarterly data, you can substitute 'month', 'year', or 'week' in place of your current 'quarter' datepart and theoretically these will work with zero additional effort.

                             

                            My best advice is to study up on logical operators, as the AND/OR conditions are not unique to Tableau. Other than that, I'd watch some of the many training videos on calculations, especially on datepart/trunc calculations. Then give yourself some personal challenges on use cases and try to get the expected results to return. You can use Superstore data if you want as it's easy for everyone here to work with.

                             

                            And if you allow me to get up on my soapbox for a minute (too late - I'm already here), thank you for embracing the "learning to fish" methodology, as many people do not have this mindset, and it makes our redundant advice and answers rather tedious (even though we're happy to help!) in addition to not progressing their skills at all. You will be a better developer/dashboard builder for it in the long run, and "future you" will thank "present you". Anyone who frequently answers questions in these forums will tell you that we don't mind providing more detailed responses in these instances because we know it's being paid attention to and adding value.

                             

                            Please ask if there's anything about the above that doesn't make sense or any follow-up questions.

                            • 11. Re: Calculated Field to Count Items Based on Dates Opened/Closed?
                              Ben Neville

                              I've mocked up an example which might help. I've included a view which shows the initiated/closed out dates for each MOC#. It has a 1 next to those which are considered Open at the end of the quarter, and a 0 which don't meet that condition. I've filtered to just the relevant dates (simply because otherwise it was hard to find the 1's in the sheet - you could remove the filters and the results would be the exact same as they are in the filtered version).

                               

                              I also included 3 versions of the calcs - the one I'd use in real life, one which was annotated (use a double forward slash to comment out the remainder of a line) to explain the calculation, though it reads horribly, and a final one which is my attempt at having "proper" indentation. Somewhere someone who writes code on a regular basis is going to see this and cringe, but since I don't think either of us would classify ourselves as "programmers", it's sufficient for our purposes which is greater clarity and legibility.

                               

                              You might need to add logic for open/closed as mine simply performs the logic of which tickets have an open date in last quarter AND either a null closed date or one which occurs in this quarter, but I think it should help illustrate my points. As before, please ask if something doesn't make sense.

                              2 of 2 people found this helpful
                              • 12. Re: Calculated Field to Count Items Based on Dates Opened/Closed?
                                dustin baker

                                I'm studying your first reply in order to make sure I understand and I will let you know if I get hung up.

                                 

                                I've downloaded the example you created but Tableau errors out when attempting to open the file because it says that the file was created by a newer version.  I'm running version 10.2.3 and when I click "Check for Product Updates" it responds that no maintenance updates were found.  Thoughts?

                                • 13. Re: Calculated Field to Count Items Based on Dates Opened/Closed?
                                  Ben Neville

                                  The updater only checks for maintenance releases (the x.x.x number). You're on the most up-to-date version of 10.2, but you haven't downloaded 10.3. Provided you have admin rights on your machine and your license is up-to-date, you definitely want 10.3! You can always check releases here, and if you want to know what you're missing, you can read about the new features in 10.3.

                                  1 of 1 people found this helpful
                                  • 14. Re: Calculated Field to Count Items Based on Dates Opened/Closed?
                                    dustin baker

                                    10-4..I will go download Version 10.3.

                                     

                                    Oh, and the mismatched quotes you referenced above were due to my transcription...but the "-1" was inside the DATETRUNC function and it was still working.  I've corrected it since then.

                                     

                                    Thanks for the information and the help (once again).  I will see if your mock-up and a little studying on the order of operations of Boolean functions will clarify this issue for me.

                                    1 2 Previous Next