1 2 Previous Next 23 Replies Latest reply on May 21, 2018 5:04 AM by Simon Runc

    Amount of tickets every day

    Juergen Reichert

      Hi,

       

      I'm new to Tableau and I have problems with my first visualisation.

       

      I want to get the amount of open tickets each day (in January).

       

       

      TicketOpenClose
      T103.01.201706.01.2017
      T205.01.201708.01.2017
      T315.01.201715.01.2017

       

       

      The result should look like:

       

                   *  * 

             *  *  *  *  *  *                    *   

      01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16

       

      Conditions

      - I cannot prepare the data!

      - I cannot use custom SQL (have to support different data sources including Excel, Text, Oracle, SQL Server, WebConnector, ... )

      - all within Tableau

       

      Using a scripting language and a chart library its done in minutes - But I cannot get it to work in Tableau.

       

      I've seen the following related Knowledge base article, but I cannot get it work.

      Showing Records That Fall Within a Period of Time | Tableau Software

      Is there a working tbwx?

       

      Any help would be highly appreciated!

        • 1. Re: Amount of tickets every day

          Hey Juergen,

           

          Have you read through this thread?

           

          Tickets Open and Closed

          • 2. Re: Amount of tickets every day
            Juergen Reichert

            Yes, I have looked into it, but it is a total different problem.

             

            I need the count of the tickets for each day (even if not ticket was opened or closed at that day).

            • 3. Re: Amount of tickets every day
              Juergen Reichert

              Is there nobody who know an answer?

              I thinks that is a really standard type a common problem.

               

              Tableau Support Team - Would you please answer?

              • 4. Re: Amount of tickets every day
                Toby Erkson

                Juergen,

                These forums are not really part of the Tableau Customer Support process.  It is a self-help area where people can ask questions and VOLUNTEERS can respond...which is anybody who is a registered user like you or me   The number of actual Tableau employees is very, very small compared to the number of non-Tableau registered users and they cannot possibly answer everybody's questions.  People are free to help whoever they want, whenever they want, if they even want to.  No SLA (Service Level Agreement), no pressure.  It is unofficial help and if the question is well worded, detailed, and [typically] includes a packaged workbook the response and probable solution is much quicker -- plus it's a great way to learn the product if you can help others!  If you need official help then you need to contact Tableau Customer Support.

                • 5. Re: Amount of tickets every day
                  Walt Reed

                  Hi Juergen,

                  Here's one way to approach it. I used the Sample Superstore Data:

                  1. Add the date to the columns shelf:

                  2. Create the following formula and substitute your field names (Open & Tickets):

                  3. Add to the viz, change to Shape, and change the Table Calculation to compute using cell:

                  4. UNCHECK aggregate measures from the Analysis menu:

                  5. Now you have your count by day with one mark per count:

                   

                  Let us know if this is what you're looking for.

                   

                  Walt

                  • 6. Re: Amount of tickets every day
                    Norbert Maijoor

                    Hi Juergen,

                     

                    Upfront! Not the final solution but an approach to be considered;) Find it below and stored in attached workbook version 9.3 located in the original thread based on scaffold Technic explained here

                     

                     

                    1. It starts with 3 files.

                    a. Scaffold file with all dates of the month

                    b. File with open dates (copy of original file)

                    b. File with close dates (copy of original file)

                     

                    The scaffold file is primary. Edit relationships between Scaffold file and Closed Date based on Date-Close and Scaffold file and Open Date based on Date-Open.

                     

                    Define calculated field: Open Tickets: if ATTR([Open Date].[Open])>=attr([Date]) then 1

                                                                                elseif ATTR([Closed Date].[Close])=attr([Date]) then -1

                                                                                else 0 END

                     

                     

                     

                     

                    Now the "dissatisfier";( Works perfect when Close Date>Open Date. On the other site...is it a realistic scenario when Close Date=Open Date;) a matter of granularity. When Close - en Open date hours are included then scaffold dataset should have dd.mm.yyyy hh:mm

                     

                    Would like to ask Simon Runc to the table.

                    Simon Runc An easter-egg is still "bothering" me concerning Close Date=Open Date issue. Any suggestions;)?

                     

                     

                    Regards,

                    Norbert

                    • 7. Re: Amount of tickets every day
                      Juergen Reichert

                      Hi Walt,

                       

                      Thanks for your answer!

                       

                      But it does not give the OPEN orders (laying between [Order Date] and [Ship Date]).

                      It also does not handle dates without orders.

                      • 8. Re: Amount of tickets every day
                        Juergen Reichert

                        Hi Norbert,

                         

                        thanks for your answer.

                        But I cannot use an scaffold file, because the time span is floating.

                         

                        I'm wondering, why this simple scenario cannot easily be solved in Tableau.

                        • 9. Re: Amount of tickets every day
                          Simon Runc

                          hi Norbert,

                           

                          So first a big shout-out for Walt's solution....I do like that...very clever!

                           

                          So using the 3 rows that Juergen posted, and following the KB article it seemed to do the trick...

                           

                          Hi Juergen, I've attached (in T10.0) a working version of the KB article...let me know if anything doesn't make sense, or it isn't doing what you want.

                          • 10. Re: Amount of tickets every day
                            Walt Reed

                            Hi Juergen,

                            You can add the dates without orders by right-clicking on the date field and checking "Show Missing Values":

                            Then, you must modify the calculation slightly (change from WINDOW_COUNT to WINDOW_SUM):

                            Now you have order dates with zero orders:

                            As far as open orders, the dataset I'm working with doesn't have open orders. If you can post an example dataset, then I'm sure we can find a way to get the calculation to work correctly.

                             

                            Walt

                            • 11. Re: Amount of tickets every day
                              Toby Erkson

                              Just because you perceive it as simple doesn't mean it is.  I can hop on a bicycle blindfolded and ride it down our driveway but my wife can't.  She can't even ride a bicycle even though it's very simple to do for most children.

                               

                              The fact that your data is rigidly set and you aren't allowed to massage it into a usable format does kind of limit what can be done.  Remember, Tableau likes data that's ready to go with minimal-to-no ETL needed.

                               

                              It looks like Walt got it but you need to add some more data per his request to help him out:  Re: Amount of tickets every day

                              • 12. Re: Amount of tickets every day
                                Juergen Reichert

                                Thanks a lot - this solved the the problem of the missing dates!

                                 

                                The Sample Superstore Data has the field 'Ship Date'.

                                I want the amount of all orders worked on (ordered but not yet shipped) for each day.

                                • 13. Re: Amount of tickets every day
                                  Walt Reed

                                  Okay, that makes sense. So I would create a calculated field which essentially filters only Orders where the ship date was not on the order date:

                                  Then, modify your calculation by substituting this field for [Order ID]:

                                  I'm attaching the modified workbook for your reference.

                                   

                                  I will echo what Toby Erkson had to say: all too often, somethign that should be 'simple' can be very complicated to do in Tableau, especially if you're trying to emulate something from an Excel workbook. Fortunately, the Tableau community is amazing and super responsive and helpful, so welcome to the club and we are glad you're here.

                                   

                                  Walt

                                  • 14. Re: Amount of tickets every day
                                    Juergen Reichert

                                    Thank you for your effort !!!

                                    It's indeed a great community and I hope I could contribute by myself in the future.

                                     

                                    But the result seems to be wrong.

                                    It shows the same as the diagramm before.

                                    Not the Open orders for each day, instead the amount opened each day (as before).

                                     

                                    e.g.

                                    January 4th -> 4 tickets instead 3

                                    January 8th -> 12 tickets instead 0

                                    1 2 Previous Next