Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
brunobertels
Specialist III
Specialist III

avg Top Five and average bottom 5 from a complexe mesure

Hi Dear All, 

Let's say i have a complexe mesure like below to calculate average call lenght : 

 

round((sum(aggr(sum({$<INDICATEURS={'Appel entrant'}>}TEMPS),SITE,FILE,LOG,NOM,DATE))
/
sum(aggr(sum({$<INDICATEURS={'Appel entrant'}>}[PROD.VOL.PROD]),SITE,FILE,LOG,NOM,DATE))
)
+
(

sum(aggr(sum({$<INDICATEURS={'Post-travail'}>}TEMPS),SITE,FILE,LOG,NOM,DATE))
/
sum(aggr(sum({$<INDICATEURS={'Post-travail'}>}[PROD.VOL.PROD]),SITE,FILE,LOG,NOM,DATE))),'00:00:01')

 

I would like to have the average top 5 call length average and bottom 5 call lenght average  

 

I'am able to rank the mesure "DMT" in a strait table but i don't know how i can have the average DMT from top 5 and bottom 5 in a KPI for exemple 

tab.PNG

 

So help may be appreciate 

Thanks in advance 

Bruno

1 Reply
Kushal_Chawda

may be below

For Top 5 -

=round(avg(aggr(if(rank((sum(aggr(sum({$<INDICATEURS={'Appel entrant'}>}TEMPS),SITE,FILE,LOG,NOM,DATE))
/
sum(aggr(sum({$<INDICATEURS={'Appel entrant'}>}[PROD.VOL.PROD]),SITE,FILE,LOG,NOM,DATE))
)
+
(
sum(aggr(sum({$<INDICATEURS={'Post-travail'}>}TEMPS),SITE,FILE,LOG,NOM,DATE))
/
sum(aggr(sum({$<INDICATEURS={'Post-travail'}>}[PROD.VOL.PROD]),SITE,FILE,LOG,NOM,DATE))),0,4)<=5,
(sum(aggr(sum({$<INDICATEURS={'Appel entrant'}>}TEMPS),SITE,FILE,LOG,NOM,DATE))
/
sum(aggr(sum({$<INDICATEURS={'Appel entrant'}>}[PROD.VOL.PROD]),SITE,FILE,LOG,NOM,DATE))
)
+
(
sum(aggr(sum({$<INDICATEURS={'Post-travail'}>}TEMPS),SITE,FILE,LOG,NOM,DATE))
/
sum(aggr(sum({$<INDICATEURS={'Post-travail'}>}[PROD.VOL.PROD]),SITE,FILE,LOG,NOM,DATE)))),NOM,DATE)),'00:00:01')

For Bottom 5-
=round(avg(aggr(if(rank(-((sum(aggr(sum({$<INDICATEURS={'Appel entrant'}>}TEMPS),SITE,FILE,LOG,NOM,DATE))
/
sum(aggr(sum({$<INDICATEURS={'Appel entrant'}>}[PROD.VOL.PROD]),SITE,FILE,LOG,NOM,DATE))
)
+
(
sum(aggr(sum({$<INDICATEURS={'Post-travail'}>}TEMPS),SITE,FILE,LOG,NOM,DATE))
/
sum(aggr(sum({$<INDICATEURS={'Post-travail'}>}[PROD.VOL.PROD]),SITE,FILE,LOG,NOM,DATE)))),0,4)<=5,
(sum(aggr(sum({$<INDICATEURS={'Appel entrant'}>}TEMPS),SITE,FILE,LOG,NOM,DATE))
/
sum(aggr(sum({$<INDICATEURS={'Appel entrant'}>}[PROD.VOL.PROD]),SITE,FILE,LOG,NOM,DATE))
)
+
(
sum(aggr(sum({$<INDICATEURS={'Post-travail'}>}TEMPS),SITE,FILE,LOG,NOM,DATE))
/
sum(aggr(sum({$<INDICATEURS={'Post-travail'}>}[PROD.VOL.PROD]),SITE,FILE,LOG,NOM,DATE)))),NOM,DATE)),'00:00:01')