12 Replies Latest reply: Dec 21, 2016 10:15 AM by Clever Anjos RSS

    Problem with function SUM()

    Eduardo DImperio

      Hi everyone,

       

       

      I finally end my code (hope so), and thanks to all that spend some time to help me.

       

       

      Now the last problem that i dont understand is using the SUN() function in the graphic.

       

       

      If you look the image for the meter 32646 i only have 1 value 0,17 so when i use SUN(CONSUMO_REAL0), I expect output 0,17.

       

       

      Why for all saints im getting 2,04 ????

       

      Consumo_real.JPG

        • Re: Problem with function SUM()
          Aehman K

          Cus it is doing sum for all related OID_METER 32646 for CONSUMO_REAL0.

           

          Maybe try Sum(Distinct(CONSUMO_REAL0) ???

          • Re: Problem with function SUM()
            Sunny Talwar

            It seems that your data is repeating for some reason. In this particular case the OID_METER might be repeating 12 times (12*0.17 = 2.04). Although you can fix this on the front end of the application, I would suggest fixing this in the back end because you don't want to carry a single row 12 times if you don't have a real use for it

              • Re: Problem with function SUM()
                Eduardo DImperio

                Hey Sunny, i saw this 12*0.17, but still dont get why this happen.

                 

                About carry a single row 12 times if i dont use SUN() its not occur. I think that will be better use Max() instead SUN()

                  • Re: Problem with function SUM()
                    Sunny Talwar

                    You have many options, you can do Only(), Max(), Avg(), Min(), Sum(Distinct...).... but all these are workarounds. None of them solves the underlying issue that you have multiple rows with same information.

                    • Re: Problem with function SUM()
                      Clever Anjos

                      12 times sounds me related to calendar problem (12 months) do you have a calendar into your app?

                      Does anytime you make a join into your script?

                        • Re: Problem with function SUM()
                          Eduardo DImperio

                          Hi Clever,

                          More or less, I get some date values, but not using a calendar.

                           

                          ANALISE_TMP:

                          LOAD

                            ID_LEITURA,

                              NAME_SYSTEM,

                              NAME_GROUP,

                              NEIGHB_SYSTEM,

                              CITY_SYSTEM,

                              OID_METER,

                              NAME_TYPE_METER,

                              TYPE_CONSTANT,

                              NAME_UC,

                              ADDRESS_BLOCK_UC,

                              DATE_READ,

                              VALUE_READ,

                              Date(DATE_READ) AS HORA,

                              If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT)  as CONSUMODIA0,

                              If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-1),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA1,

                              If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-2),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA2,

                              If(OID_METER=  Peek(OID_METER) and(Peek(VALUE_READ)- VALUE_READ)=0,OID_METER)  as EXCLUDE_METER

                              RESIDENT TMP2

                              ORDER BY OID_METER, DATE_READ DESC;

                           

                          DROP TABLE TMP,TMP2;

                           

                           

                          Exclusao:

                          LOAD

                            OID_METER,

                            EXCLUDE_METER

                          RESIDENT ANALISE_TMP

                          WHERE NOT EXISTS (EXCLUDE_METER,OID_METER);

                           

                           

                          INNER JOIN (Exclusao)

                           

                          LOAD

                            OID_METER,

                              ID_LEITURA,

                              NAME_SYSTEM,

                              NAME_GROUP,

                              NEIGHB_SYSTEM,

                              CITY_SYSTEM,

                              NAME_TYPE_METER,

                              TYPE_CONSTANT,

                              NAME_UC,

                              ADDRESS_BLOCK_UC,

                              DATE_READ AS DATA,

                              VALUE_READ,

                              CONSUMODIA0,

                              CONSUMODIA1,

                              CONSUMODIA2,

                              If (OID_METER =PEEK(OID_METER), PEEK(CONSUMODIA0)- CONSUMODIA0) AS CONSUMO_REAL0,

                              If (OID_METER =PEEK(OID_METER), PEEK(CONSUMODIA1)- CONSUMODIA1) AS CONSUMO_REAL1,

                              If (OID_METER =PEEK(OID_METER), PEEK(CONSUMODIA2)- CONSUMODIA2) AS CONSUMO_REAL2,

                              HORA

                              RESIDENT ANALISE_TMP 

                              WHERE   (HOUR(HORA)=2 OR HOUR(HORA)=5)

                              ORDER BY OID_METER DESC;

                           

                           

                             DROP TABLE ANALISE_TMP;

                             

                          Teste:

                           

                          LOAD

                            OID_METER,

                              ID_LEITURA,

                              NAME_SYSTEM,

                              NAME_GROUP,

                              NEIGHB_SYSTEM,

                              CITY_SYSTEM,

                              NAME_TYPE_METER,

                              TYPE_CONSTANT,

                              NAME_UC,

                              ADDRESS_BLOCK_UC,

                              DATA,

                              VALUE_READ,

                              CONSUMO_REAL0,

                              CONSUMO_REAL1,

                              CONSUMO_REAL2,

                              HORA AS HORA_2

                              RESIDENT Exclusao

                              ORDER BY OID_METER DESC;

                           

                           

                          DROP TABLE Exclusao;

                           

                           

                          EXIT SCRIPT

                            • Re: Problem with function SUM()
                              Clever Anjos

                              Try with this (if make sense)

                              Teste:

                               

                              LOAD DISTINCT

                                OID_METER,

                                  ID_LEITURA,

                                  NAME_SYSTEM,

                                  NAME_GROUP,

                                  NEIGHB_SYSTEM,

                                  CITY_SYSTEM,

                                  NAME_TYPE_METER,

                                  TYPE_CONSTANT,

                                  NAME_UC,

                                  ADDRESS_BLOCK_UC,

                                  DATA,

                                  VALUE_READ,

                                  CONSUMO_REAL0,

                                  CONSUMO_REAL1,

                                  CONSUMO_REAL2,

                                  HORA AS HORA_2

                                  RESIDENT Exclusao

                                  ORDER BY OID_METER DESC;

                      • Re: Problem with function SUM()
                        vj kri

                        Hi,

                         

                        The reason for repetiton is due the underlying data modelling.

                        1) the table which contains OID_METER,Consumo_real0 columns might be joined with other tables, hence causing duplicate records

                        2)the columns OID_METER and Consumo_real0 might be in different tables and are associated using a common key which might have 12 entries.