¿How to count a measure?

[#1]

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:

ID	Displacement time	Categoria del sitio	(if((interval(AVG({$<[Categoria del sitio]={'C1'}……..
3206	0	C2	Cumple SLA
3207	0	C2	Cumple SLA
3208	0	C2	Cumple SLA
3209	0	C5	Cumple SLA
3211	0	C2	Cumple SLA
3213	0	C2	Cumple SLA
3218	16.63	C4	Incumple SLA
3222	20.67	C2	Incumple SLA
3282	2.77	C4	Incumple SLA
6972	1.50	C2	Incumple SLA
6976	16.47	C2	Incumple SLA
6982	0	C2	Cumple SLA
6984	25.40	C1	Incumple SLA
6988	11.27	C2	Incumple SLA
6991	12.62	C4	Incumple SLA
7199	2.43	C2	Incumple SLA
7204	1.75	C2	Incumple 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.

[Oleg_Troyansky · Accepted solution]

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

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

[#4]

I have a error, "Error in expresion"

[#5]

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

))

[#6]

Thanks you! Thats right!

How i cant reserve my seat?

Thansk again!

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