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

    calculated date x axis

    Steffen Lerbs

      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


        • 1. Re: calculated date x axis
          Tracy Rodgers

          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]



          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!



          • 2. Re: calculated date x axis
            Steffen Lerbs

            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


            • 3. Re: calculated date x axis
              Tracy Rodgers

              Hi Steffen,


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