Impromptu - delta datetime

[GilMerc]

Hi,

I find how I can compare a data field with the same data field but for a previous occurrence. To goal is to show a delta between to datetime.

For exemple :

Action Time Delta
1 today at 9h00 -
1 today at 12h00 3h00
2 today at 10h00 -
2 today at 10h30 0h30
2 today at 14h00 3h30

Anybody know how to do this.

Thank's in advance,
Gilles.

 

[Recce]

Hi GilMerc,

This is just a quick work around but I think that Dave will be able to give a more permanent solution. Firstly, 3 calculations,
first calc: Rank(Time)
second calc: if rank = 1 the Time else if rank = 2 then Time
third calc: max(sec calc) - min (sec calc)

I'll try and work on a better solution.

 

[GJParker]

Giles


As long as the Time values are proper times or dateTime values, You should be able to group by Action then create a summary formulation

Minimum(Time)

Then Create a Second Formula Delta

Time - Minimum(Time)

HTH



Gary Parker
Systems Support Analyst
Manchester, England

 

[Recce]

Good one GJParker. Even better

 

[GilMerc]

Hi,

It's working but not for my solution, if you see the example I want a delta between actual time and the last time before. For action 2, delta is 3h30 (14h00-10h30, the time minus last entry before it) and not 4h00 (where the calculation is 14h00-10h00, enty minus the min of action).

I find again a solution.

Thank's, Gilles

 

[GilMerc]

Hi,

I found this description in knowledge base of cognos site. This solution work with number and I don't know how to take it with date.

Gilles.

===========================================================
Problem Description

How can you calculate the increased percentage of a column value from one line to another?

Example: you would like to obtain the following result :

----------|-----------|--------------------------------
QUARTER | AMOUNT | % of Increase between N-1 and N
----------|-----------|--------------------------------
98'Q1 | 100 |
----------|-----------|--------------------------------
98'Q2 | 120 | 20.00 %
----------|-----------|--------------------------------
98'Q3 | 140 | 16.67 %
----------|-----------|--------------------------------
98'Q4 | 110 | - 21.43 %
----------|-----------|--------------------------------
99'Q1 | 100 | - 9.09 %
----------|-----------|--------------------------------
99'Q2 | 130 | 30.00 %


Solution Description

You must use the following calculated column for column " % Increase between N-1 and N" :

( A - ( moving-total ( A ; 2 ) for report - A ) ) / if ( ( moving-total ( A ; 2 ) for report - A ) <> 0 ) then ( moving-total ( A ; 2 ) for report - A ) else ( 1 ) ...


and use the display format " 0.00 % " for the calculated column .

To calculate the AMOUNT difference between line N-1 and line N ( 'delta' between the two lines) <not the percentage, only the difference>, use the first term of the calculated column.

Example :

A - ( moving-total ( A ; 2 ) for report - A )

==========================================================

 

[drlex]

Am I missing something or would running-difference not be what is required here?
Group by Action, order by time and delta = running-difference(time)

soi la, soi carre

 

[GJParker]

Running-Difference only works with numeric values and not times, you would somehow have to convert your times to numbers, then convert the difference back to hours and minutes.

You should be able to do this if all of your times are on the same day.

Gary Parker
Systems Support Analyst
Manchester, England

 

[griffindm]

I've looked at this for a bit, and did some research on other database fora here. I see no simple elegant solution. If this is a recurring request (rather than a 1-off), I would consider creating (or having your IT group create) a reporting table that stages the data and creates the row time difference as a row attribute. Impromptu is not particularly good are doing row-difference calculations (for that matter, neither is any other query-based SQL tool).

Let me know if you need further advice on this approach, and what your database platform is.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[GJParker]

Just FYI

Crystal Reports has 2 functions Previous and Next which does read either the previous or next records in rows of data.



Gary Parker
Systems Support Analyst
Manchester, England