Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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)
I have a error, "Error in expresion"
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'))))))
))
Thanks you! Thats right!
How i cant reserve my seat?
Thansk again!
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))