Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

¿How to count a measure?

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: ¿How to count a measure?

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

6 Replies
MVP
MVP

Re: ¿How to count a measure?

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?

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)


talk is cheap, supply exceeds demand
Not applicable

Re: ¿How to count a measure?

I have a error, "Error in expresion"

Not applicable

Re: ¿How to count a measure?

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'))))))

))

Not applicable

Re: ¿How to count a measure?

Thanks you! Thats right!

How i cant reserve my seat?

Thansk again!

Re: ¿How to count a measure?

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))

Community Browser