How to get Top 20 task hours in a Table of qliksense

[niha]

Hi,

I have Region, task  name as dimension and total MSL hours as measures. The code of measure is 
(sum({<FM={'MSL'}>}Q1hours)+sum({<FM={'MSL'}>}Q2hours)+sum({<FM={'MSL'}>}Q3hours)+sum({<FM={'MSL'}>}Q4hours)). There are say 5 region , NA, Japan, Apac, LATAM, EMEA. In the next colums , I want to get TOP 20 Task ' hours of each region . I might not need total msl hours of all task as seen but I need only TOP 20 task ' MSL  hours of each region . I am guessing it will use AGGR or Firstsort values but unable to complete it. Please suggest.

Thanks,

Niha

[Apprentice4life]

You can try combination of the AGGR and RANK functions.

Example:

=AGGR(IF(Rank(SUM(Equation))<=20 ,SUM(Equation)), Region)

[niha]

Thanks a lot! I tried your suggestion.

=AGGR(IF(Rank(
SUM({<FM={'MSL'}>Q1hours)
+SUM({<FM={'MSL'}>}Q2hours)+
SUM({<FM={'MSL'}>}Q3hours)+
SUM({<FM={'MSL'}>}Q4hours))<=20,

SUM({<FM={'MSL'}>Q1hours)+sum({<FM={'MSL'}>}Q2hours)+
sum({<FM={'MSL'}>}Q3hours)+sum({<FM={'MSL'}>}Q4hours)),Region)

But I am not getting any values.

[niha]

Hello,

 May be Aggr not necessary as in Table there is a option of  Top or Botton fixed values and it selects only top 20 task. 

Thanks.

[patilamay]

Use this expression as a dimension instead of measure.

And in the expression, if condition is true use the dimension field.

=AGGR(IF(Rank(Sum(expression))<=20 ,Dimension), Dimension). Supress null values.

HTH

Thanks,

[niha]

Thanks. I tried yours.  Also suppressed null. no luck.

=AGGR(IF(Rank(sum({<FM={'MSL'}>}Q1hours)+sum({<FM={'MSL'}>}Q2hours)+sum({<FM={'MSL'}>}Q3hours)+sum({<FM={'MSL'}>}Q4hours))<=20,[Task Name]),Region)

[patilamay]

Try this and suppress nulls.

AGGR(IF(Rank(sum({<FM={'MSL'}>}Q1hours)+sum({<FM={'MSL'}>}Q2hours)+sum({<FM={'MSL'}>}Q3hours)+sum({<FM={'MSL'}>}Q4hours))<=20,Region),Region)

Thanks

[niha]

Hi,

I tried and it showed Region name instead of hour. Nevermind I got the top 20 task hour with out AGGR function as there is a option of selction Top 20 

Now I got this Table.  I have to write a measure that should convert percentage of total. I tried like this but nor correct result.. Here I am trying to pull the percentage of taskhours to total msl hours.

=(sum({<FM={'MSL'},[Task Name]={"*"}>}Q1hours)+
sum({< FM={'MSL'},[Task Name]={"*"}>}Q2hours)+
sum({<FM={'MSL'},[Task Name]={"*"}>}Q3hours)+
sum({<FM={'MSL'},[Task Name]={"*"}>}Q4hours)

)
/
(sum({<FM ={'MSL'}>}Q1hours)+sum({< FM ={'MSL'}>}Q2hours)+sum({<FM ={'MSL'}>}Q3hours)+ sum({<FM ={'MSL'}>}Q4hours)

)

Thanks,

Niha

[patilamay · Accepted solution]

ok that's cool.

For percentage maybe use below expression

=(sum({<FM={'MSL'}>}Q1hours)+sum({< FM={'MSL'}>}Q2hours)+sum({<FM={'MSL'}>}Q3hours)+sum({<FM={'MSL'},>}Q4hours))
/
(sum(total {<FM ={'MSL'}>}Q1hours)+sum(total {< FM ={'MSL'}>}Q2hours)+sum(total {<FM ={'MSL'}>}Q3hours)+ sum(total {<FM ={'MSL'}>}Q4hours))

Thanks,

[niha]

It worked. you made my day!👌