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

# calculated date x axis

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.

Steffen

• ###### 1. Re: calculated date x axis

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

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

Steffen

• ###### 3. Re: calculated date x axis

Hi Steffen,

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

-Tracy