3 Replies Latest reply on Feb 21, 2014 9:35 AM by Tracy Rodgers

    calculated date x axis

    Steffen Lerbs
    Steffen Lerbs Feb 13, 2014 8:23 AM

      Hello Everyone,

       

      i am trying to achieve the following:

       

      Management wants to see a graph which displays the trend by day (for last x amount of days) of Open Customer Tickets in our system.

      For that there are 2 status available, simply Open or Closed. In order to show how many tickets are Open today, i just need to show current Status being OPEN. For the day of yesterday i need to use a formula:

      Open Tickets Today minus Created Tickets Yesterday + Resolved Tickets Yesterday = Open Tickets Yesterday.

      The problem is that tickets are changing their status and therefore i need to use the calculation above.

       

      My question is how do i "assign" the date of today to my calculated field of Open Tickets today and how do i "assign" the date of yesterday to the calculated Field Open Tickets Yesterday to present it in a graph.

       

      Additionally i have created a parameter that defines how many days before today i want to show the amount of Open tickets. It works fine by slightly adjusting the formula above. I still have then the issue of using the 2 dates (today and the date of the day i am looking at) on the X axis.

       

      I hope someone can help with this issue as it would solve similar issues i have in other reports.

       

      thanks in advance

      Steffen

        • 1. Re: calculated date x axis
          Tracy Rodgers
          Tracy Rodgers Employee Feb 20, 2014 9:55 AM (in response to Steffen Lerbs)

          Hi Steffen,

           

          One way you could do this would be to create a calculated field that combines today and yesterday similar to:

           

          if [Date]=today() then [Open Tickets Today]

          elseif [Date]=dateadd('day', -1, today()) then [Open Tickets Today] - [Created Tickets Yesterday] + [Resolved Tickets Yesterday]

          end

           

          If you want to be able to change the number of days before today that you want to look at, replace "-1" with the parameter.

           

          Hope this helps!

           

          -Tracy

          • 2. Re: calculated date x axis
            Steffen Lerbs
            Steffen Lerbs Feb 20, 2014 11:44 PM (in response to Tracy Rodgers)

            Hello Tracy,

             

            thank you for your reply. I have managed to correctly calculate the 2 amounts for Open tickets. The remaining challenge is to present the data in a graph with the correct date on the axis. I am not capable of showing that in a graph.

             

            i hope you have an idea

             

            thank you in advance

            Steffen

            • 3. Re: calculated date x axis
              Tracy Rodgers
              Tracy Rodgers Employee Feb 21, 2014 9:35 AM (in response to Steffen Lerbs)

              Hi Steffen,

               

              Are you able to post the packaged workbook (or a sample workbook) showing the issue?

               

              -Tracy