# Qlik Sense App Development

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:

 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.

1 Solution

Accepted Solutions
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!

Cheers,

Oleg Troyansky

6 Replies
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!

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!

MVP

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