4 Replies Latest reply on Sep 16, 2018 10:32 AM by Don Wise

# Trend Over Time of Calculated Field related to date

Hello everyone,

I have a question about a trend over time referred to a Calculated Field related to dates.

I try to explain my issue making a small foreword:

- I have Service Orders (SO) related to failures on airplanes

- every SO has an opening date and, if already closed, a closing date

- every SO has a criticality value (from 1 to 3) related to the gravity and urgency of the SO (1 is the most critical)

- for every SO I have defined a CRITICALITY INDEX, that is: GdA*(4^-(c))

where:

GdA = Days till the SO is Open

c = criticality value (1 to 3)

- the SUM of the Criticality Index of every SO in open status on a airplane, indetify the Criticality status of every airplane to understand which one is more critical than others.

WHERE I AM:

I can represent the actual status of the Criticality Index, comparing the planes each others ( so related to TODAY() ).

REQUEST:

I can't understand if the index is getting better or worse over time.

So, for every airplane I need to calculate the CRITICALITY INDEX over time (no more related to TODAY but depending on the abscissa).

This is the result that I have using Excel  (sum  of all airplanes):

In attach

the Example1.TWBX where you can find the calculation and first graph set up.

and

the Example1.XLSX where I calculated the Critical Index over time  using excel formulas and where you can also find the data input.

I hope some of you can help me.

Thanks in advance for the feedback.

Cheers

• ###### 2. Re: Trend Over Time of Calculated Field related to date

Hello Emanuele,

If I understand your need correctly, are you looking for something like the below? I reattached your workbook with the new worksheet.  If yes, please mark this response as correct so that others may find it useful in the future.  Thx, Don

• ###### 3. Re: Trend Over Time of Calculated Field related to date

Hi Don,

No unfortunately not.

The graph should be the same as the one I posted below in the post.

Let's make an example with just one Service Order in priority 1.

If I raise a Service Order the August 1st, the same day its criticaliy-index will be 0. After 1 day it will encrease using the formula (opening-time)*4^-(priority)=1*4^-1=0,25, after 2 days increase again = 0,5. And so on... so I need to compare the creating date and the value of the x-axis, and the closing date because if the Service Order will be closed on the 10th day... later on this service order will have a criticality = 0.

In the graph the value at the first date should be 0.

Is it more clear now?

• ###### 4. Re: Trend Over Time of Calculated Field related to date

Hi Emanuele,

I wasn't able to replicate your underlying calculations for COUNTIFS against the data set for Calc-Graph. You could try this approach, so long as you're continuing to produce the underlying worksheet for existing chart called Calc-Graph.