14 Replies Latest reply: Jan 8, 2017 2:30 PM by Fabio Mazzarelli RSS

    COUNT

    Fabio Mazzarelli

      Hi Community

       

      count({<PRIORITY = {'Low'}>,<TEMPOTRASCORSO = {'>=0 AND <=24'}>} [TICKET])

      count({<PRIORITY = {'Low'}>,<TEMPOTRASCORSO = {'>=25 AND <=50'}>} [TICKET])

      count({<PRIORITY = {'Low'}>,<TEMPOTRASCORSO = {'>=51 AND <=100'}>} [TICKET])

       

       

      EXAMPLE

      TABLE

      PRIORITY     TEMPOTRASCORSO

      low               34

      low               1

      low               292

      normal          23

      normal          2

      urgent          3

      urgent          40

      urgent           455

       

      Normally I count(PRIORITY) but this time I need to count how many TICKET fall in the range indicated TEMPO TRASCORSO having priority Low or normal or urgent

       

      Any suggest?

        • Re: COUNT
          Sunny Talwar

          Are you looking for or condition?

           

          count({<PRIORITY = {'Low'}>+<TEMPOTRASCORSO = {'>=0<=24'}>} [TICKET])

          count({<PRIORITY = {'Low'}>+<TEMPOTRASCORSO = {'>=25<=50'}>} [TICKET])

          count({<PRIORITY = {'Low'}>+<TEMPOTRASCORSO = {'>=51<=100'}>} [TICKET])

           

          Or this?

           

          count({<PRIORITY = {'Low'}, TEMPOTRASCORSO = {'>=0<=24'}>} [TICKET])

          count({<PRIORITY = {'Low'}, TEMPOTRASCORSO = {'>=25<=50'}>} [TICKET])

          count({<PRIORITY = {'Low'}, TEMPOTRASCORSO = {'>=51<=100'}>} [TICKET])

            • Re: COUNT
              Fabio Mazzarelli

              Hi,

              i'm looking for.

              No, it doesn't work.

               

              The field TEMPOTRASCORSO is a calculated field

              LOAD

              .....

              DTDELTA

              .......

              num(SubField((DTDELTA),' ',1))*24 + num(SubField(SubField((DTDELTA),' ',2),':',1))  AS TEMPOTRASCORSO";

              ;

               

              and DTDELTA

              contains 2 03:01 ................ 2 days 03 hours 01 minute

            • Re: COUNT
              Shraddha Gajare

              TEMPOTRASCORSO is calculated on front end under Master measure or in Load script?

              • Re: COUNT
                Vineeth Pujari

                TRY

                 

                count({< [TICKET] = {"=PRIORITY = 'Low' or (TEMPOTRASCORSO >=0 AND TEMPOTRASCORSO <=24)"}>} [TICKET])

                count({< [TICKET] = {"=PRIORITY = 'Low' or (TEMPOTRASCORSO >=25 AND TEMPOTRASCORSO <=50)"}>} [TICKET])

                count({< [TICKET] = {"=PRIORITY = 'Low' or (TEMPOTRASCORSO >=51 AND TEMPOTRASCORSO <=100)"}>} [TICKET])

                  • Re: COUNT
                    Fabio Mazzarelli

                    Hi,

                     

                     

                    TempoTrascorso: 49 (hours)

                    DTDELTA: 02 01:05 (2 days 05 hours 01 minute ...... 24 h + 24 h + 1 = 49 hours

                     

                    Entro 4h .......

                    Formula;

                    count({< [PROGRESSIVO SEGNALAZIONI] = {"=PRIORITY = 'Low' AND (TempoTrascorso >=0 AND TempoTrascorso <=4)"}>} [PROGRESSIVO SEGNALAZIONI])

                     

                    The value 1 should be placed in the column 'Oltre 8' h and not 'Entro 4h'

                     

                    Where might be the error?

                     

                    Immagine.png

                      • Re: COUNT
                        Sunny Talwar

                        Try this:

                         

                        Count({< [PROGRESSIVO SEGNALAZIONI] = {"=PRIORITY = 'Low' AND (TempoTrascorso >= 0 and TempoTrascorso <=4/24)"}>} [PROGRESSIVO SEGNALAZIONI])

                         

                        Since your TempoTrascorso is time field, it is in decimals which is why when you compare it to integers, most of them fall under 0 and 4 (unless something is greater than 4 days. If you are checking if TempoTrascorso is between 0 hours and 4 hours, try dividing these numbers by total number of hours in the day (which is 24) to get them hour equivalent.

                        • Re: COUNT
                          Vineeth Pujari

                          Use the same expression in Set Analysis or Preferably create a field

                          in loadScript for TempoTrascorso example

                          interval(interval#('TempoTrascorso ','d mm:hh:ss'),'h') as Hrs_TempoTrascorso;


                          for now try below;

                           

                          count({< [PROGRESSIVO SEGNALAZIONI] = {"=PRIORITY = 'Low' AND (interval(interval#('TempoTrascorso ','d mm:hh:ss'),'h')>=0 AND interval(interval#('TempoTrascorso ','d mm:hh:ss'),'h')<=24)"}>} [PROGRESSIVO SEGNALAZIONI])


                          After you create a numeric field use

                          count({< [PROGRESSIVO SEGNALAZIONI] = {"=PRIORITY = 'Low' AND Hrs_TempoTrascorso>=0 AND Hrs_TempoTrascorso<=24)"}>} [PROGRESSIVO SEGNALAZIONI])


                            • Re: COUNT
                              Fabio Mazzarelli

                              Hi,

                              in loadscript already exist

                                   DTDELTA  AS DELTA,

                                  interval(Interval#([DTDELTA],'D hh:mm'),'h') as TempoTrascorso,

                               

                                  yours ....interval(interval#('TempoTrascorso ','d mm:hh:ss'),'h') as TempoTrascorso1,

                               

                              and TempoTrascorso contains values converted in hour (0, 1, 2, 3, 35, 96 ....)

                               

                              But formula

                              count({< [PROGRESSIVO SEGNALAZIONI] = {"=PRIORITY = 'Low' AND (TempoTrascorso)>=0 AND (TempoTrascorso)<=4"}>} [PROGRESSIVO SEGNALAZIONI])

                               

                              consider 4 day instead of 4h.

                              So that my column

                              'Entro 4 h'

                              count 1 until 96 and then 0.

                              instead of count should be 4 until 4 and then 0