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

    Difference with previous value

    Luca V

      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
          Sunny Talwar

          Try this:

           

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

            • Re: Difference with previous value
              Sunny Talwar

              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
                Luca V

                You're answer is correct.

                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 ?

                 

                 

                a.jpg

                  • Re: Difference with previous value
                    Sunny Talwar

                    Check this:

                     

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

                      • Re: Difference with previous value
                        Luca V

                        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
                            Sunny Talwar

                            Is this what you are looking to get?

                             

                            Capture.PNG

                             

                            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
                                Luca V

                                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
                                  Andrea Martini

                                  Hi,

                                   

                                  Can you please help me find a solution to this problem?

                                  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.

                                   

                                  Thank you in advance