8 Replies Latest reply: Feb 12, 2018 10:27 AM by Andrea Martini

# Difference with previous value

Hi,

is it possible to calcolate at runtime the difference between values in a chart.

For example, i have the next table data.

PROGRESSIVE,TYPE,DEVICE,ENERGY

1,1,a,1

1,2,b,2

1,3,c,3

1,4,d,4

2,1,a,5

2,2,b,6

2,3,c,7

2,4,d,8

3,1,a,9

3,2,b,10

3,3,c,11

3,4,d,12

In a chart i would like to set as DIMENSION "progressive" and "device"

In the EXPRESSION  i would like something like  "ENERGY - previous(ENERGY)" to calculate the difference with the previous value:

PROGRESSIVE "2" - DEVICE "a"  --->  5 - 1 = 4

PROGRESSIVE "2" - DEVICE "b"  ---->  6 - 2  = 4

......

PROGRESSIVE "3" - DEVICE "d"  ----->  12 - 8 = 4

is it possible ?

• ###### Re: Difference with previous value

Try this:

Aggr(ENERGY- Above(ENERGY), DEVICE, PROGRESSIVE)

• ###### Re: Difference with previous value

And if you have QV12, you can try this (which would work even when the PROGRESSIVE is not sorted ascending while loading the data -> The sortable Aggr function is finally here!)

Aggr(ENERGY- Above(ENERGY), DEVICE, (PROGRESSIVE, (NUMERIC)))

• ###### Re: Difference with previous value

Is it also possible set a condition that verify dimensions ?

For example:

If i select PROGRESSIVE values :

7,8,9,12,13,14

the ENERGY PRODUCTED on 12 is (value of "12" - value of "9")

i would like to check that dimensions must be progressive althougt i would like to show a null value

Is it possible ?

• ###### Re: Difference with previous value

Check this:

Aggr(If(PROGRESSIVE = Above(PROGRESSIVE) + 1,  ENERGY- Above(ENERGY)), DEVICE, PROGRESSIVE)

• ###### Re: Difference with previous value

I'm trying to apply to my real case but i can't.
You can also solve this:

For every hour and every device i need the max value and make the difference with the previous value.
Then i need to sum the values group by TYPE.

Like the EXAMPLE.

• ###### Re: Difference with previous value

Is this what you are looking to get?

Expression:

=If(Floor(Avg(DATETIME)) + Hour(Avg(DATETIME))/24 - Above(Floor(Avg(DATETIME)) + Hour(Avg(DATETIME))/24) > MakeTime(0, 59) and

Floor(Avg(DATETIME)) + Hour(Avg(DATETIME))/24 - Above(Floor(Avg(DATETIME)) + Hour(Avg(DATETIME))/24) < MakeTime(1, 1),

Sum(Aggr(Max({<[ENERGY2]= {"=Max([ENERGY2])>0"}, [TYPE]= {"=Min([TYPE])=1"}>} [ENERGY2])-Above(Max({<[ENERGY2]= {"=Max([ENERGY2])>0"}, [TYPE]= {"=Min([TYPE])=1"}>} [ENERGY2])), IDDEVICE, DAY, HOUR)))

I think your if statement was not completely right. You were using DATETIME - Above(DATETIME) = 1, but remember this would give you a number in decimal if DATETIME would be your dimension (which in case was not). So a more complicated if statement was needed here.

I had to use greater and less than conditions because of rounding error issues when dealing with decimals (Rounding Errors)

Let me know if above is not what you were hoping to get.

Best,

Sunny

• ###### Re: Difference with previous value

Interesting . This works !
I was pretty sure that with datetime the functiont " -1" didn't work but i don't know how to do.

Thank you

• ###### Re: Difference with previous value

Hi,

I have this set of data:

OPERATOR, DATE

 Andrew 01-DEC-17 10:40:17 00:01:19 Andrew 01-DEC-17 10:41:36 00:05:45 Andrew 01-DEC-17 10:47:21 00:00:41 Andrew 01-DEC-17 10:48:02 00:03:03 Nicole 01-DEC-17 10:51:05 00:00:05 Nicole 01-DEC-17 10:51:10 00:00:05 Andrew 02-DEC-17 10:51:15 00:02:55 Andrew 02-DEC-17 10:54:10 00:01:30 Sophie 02-DEC-17 10:55:40 00:01:41 Sophie 02-DEC-17 10:57:21 00:01:33

For each operator i would like to get the differnce between every DATE with the previous one and them sum all of these times in order to get the final lead time; for example:

Andrew, 01-DEC

1st activity 10:41:36 - 10:40:17 --> 00:01:19

2nd activity 10:47:21 - 10:41:36 --> 00:05:45

3rd activity 10:48:02 - 10:47:21 --> 00:00:41

The total amount of time is the sum of the results above

I need to make this in backend.