6 Replies Latest reply: Apr 24, 2016 6:38 PM by Robert Mika RSS

    ¿How to count a measure?

    Sebastián Garcíá

      Hi there,

       

      I have some values and fields in a Excel File. I make a formule to calculate the SLA, the data is like this:

       

       

      IDDisplacement timeCategoria del sitio(if((interval(AVG({$<[Categoria del sitio]={'C1'}……..
      32060C2Cumple SLA
      32070C2Cumple SLA
      32080C2Cumple SLA
      32090C5Cumple SLA
      32110C2Cumple SLA
      32130C2Cumple SLA
      321816.63C4Incumple SLA
      322220.67C2Incumple SLA
      32822.77C4Incumple SLA
      69721.50C2Incumple SLA
      697616.47C2Incumple SLA
      69820C2Cumple SLA
      698425.40C1Incumple SLA
      698811.27C2Incumple SLA
      699112.62C4Incumple SLA
      71992.43C2Incumple SLA
      72041.75C2Incumple SLA

       

      The formule is:

       

      (if((interval(AVG({$<[Categoria del sitio]={'C1'},[Status]={'Closed'},[Tracker]={'CM'}>}[Displacement time]), 'hh:mm:ss'))<='1','Cumple SLA',

        if((interval(AVG({$<[Categoria del sitio]={'C2'},[Status]={'Closed'},[Tracker]={'CM'}>}[Displacement time]), 'hh:mm:ss'))<='3','Cumple SLA',

          if((interval(AVG({$<[Categoria del sitio]={'C3'},[Status]={'Closed'},[Tracker]={'CM'}>}[Displacement time]), 'hh:mm:ss'))<='3','Cumple SLA',

              if((interval(AVG({$<[Categoria del sitio]={'C4'},[Status]={'Closed'},[Tracker]={'CM'}>}[Displacement time]), 'hh:mm:ss'))<='8','Cumple SLA',

                  if((interval(AVG({$<[Categoria del sitio]={'C5'},[Status]={'Closed'},[Tracker]={'CM'}>}[Displacement time]), 'hh:mm:ss'))<='12','Cumple SLA','Incumple SLA'))))))

       

      The formule works fine!

       

      I need to cout "Cumple SLA". I tried making a calculate dimension, making a variable, but does not work!

       

      The result should be: 7.

       

      DATA SOURCE: Excel File

      Version: QlikView or QlikSense.

       

      Thanks!!!

       

      Best regards.

        • Re: ¿How to count a measure?
          Oleg Troyansky

          For this calculation, you need to use Advanced Aggregation (the AGGR() function), with the same expression and the dimensions that determine at what level the SLA needs to be calculated, as the AGGR() dimensions:

           

          count(

               AGGR(

                    IF( <your formula> = 'Cumple SLA', 1) ,

                    ID, ... other dimensions

               )

          )

           

          I'm going to teach a technical session "Understanding Advanced Aggregation (AGGR)" at Qonnections 2016 on Wednesday, May 4th - if you are coming to connections, reserve your seat!

           

          You can also learn more about Advanced Aggregation from my lecture at the Masters Summit for Qlik (coming soon to Austin, TX), or from my new book QlikView Your Business.

           

          Cheers,

          Oleg Troyansky

            • Re: ¿How to count a measure?
              Sebastián Garcíá

              I have a error, "Error in expresion"


              count(

                   AGGR((if((interval(AVG({$<[Categoria del sitio]={'C1'},[Status]={'Closed'},[Tracker]={'CM'}>}[Displacement time]), 'hh:mm:ss'))<='1','Cumple SLA',

                if((interval(AVG({$<[Categoria del sitio]={'C2'},[Status]={'Closed'},[Tracker]={'CM'}>}[Displacement time]), 'hh:mm:ss'))<='3','Cumple SLA',

                  if((interval(AVG({$<[Categoria del sitio]={'C3'},[Status]={'Closed'},[Tracker]={'CM'}>}[Displacement time]), 'hh:mm:ss'))<='3','Cumple SLA',

                      if((interval(AVG({$<[Categoria del sitio]={'C4'},[Status]={'Closed'},[Tracker]={'CM'}>}[Displacement time]), 'hh:mm:ss'))<='8','Cumple SLA',

                          if((interval(AVG({$<[Categoria del sitio]={'C5'},[Status]={'Closed'},[Tracker]={'CM'}>}[Displacement time]), 'hh:mm:ss'))<='12','Cumple SLA','Incumple SLA'))))))

              ))

              • Re: ¿How to count a measure?
                Sebastián Garcíá

                Thanks you! Thats right!

                 

                How i cant reserve my seat?

                 

                Thansk again!

              • Re: ¿How to count a measure?
                Gysbert Wassenaar

                Perhaps like this:

                 

                sum(aggr(

                (if((interval(AVG({$<[Categoria del sitio]={'C1'},[Status]={'Closed'},[Tracker]={'CM'}>}[Displacement time]), 'hh:mm:ss'))<='1',1,

                  if((interval(AVG({$<[Categoria del sitio]={'C2'},[Status]={'Closed'},[Tracker]={'CM'}>}[Displacement time]), 'hh:mm:ss'))<='3',1,

                    if((interval(AVG({$<[Categoria del sitio]={'C3'},[Status]={'Closed'},[Tracker]={'CM'}>}[Displacement time]), 'hh:mm:ss'))<='3',1,

                        if((interval(AVG({$<[Categoria del sitio]={'C4'},[Status]={'Closed'},[Tracker]={'CM'}>}[Displacement time]), 'hh:mm:ss'))<='8',1,

                            if((interval(AVG({$<[Categoria del sitio]={'C5'},[Status]={'Closed'},[Tracker]={'CM'}>}[Displacement time]), 'hh:mm:ss'))<='12',1,0))))))

                ,ID)

                  • Re: ¿How to count a measure?
                    Sebastián Garcíá

                    I have a error, "Error in expresion"

                      • Re: ¿How to count a measure?
                        Robert Mika

                        Add one more bracket at the end

                        sum(aggr(

                        (if((interval(AVG({$<[Categoria del sitio]={'C1'},[Status]={'Closed'},[Tracker]={'CM'}>}[Displacement time]), 'hh:mm:ss'))<='1',1,

                          if((interval(AVG({$<[Categoria del sitio]={'C2'},[Status]={'Closed'},[Tracker]={'CM'}>}[Displacement time]), 'hh:mm:ss'))<='3',1,

                            if((interval(AVG({$<[Categoria del sitio]={'C3'},[Status]={'Closed'},[Tracker]={'CM'}>}[Displacement time]), 'hh:mm:ss'))<='3',1,

                                if((interval(AVG({$<[Categoria del sitio]={'C4'},[Status]={'Closed'},[Tracker]={'CM'}>}[Displacement time]), 'hh:mm:ss'))<='8',1,

                                    if((interval(AVG({$<[Categoria del sitio]={'C5'},[Status]={'Closed'},[Tracker]={'CM'}>}[Displacement time]), 'hh:mm:ss'))<='12',1,0))))))

                        ,ID))