Announcements
cancel
Showing results for
Did you mean:
Creator II

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

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

1 Solution

Accepted Solutions
Contributor III

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,

9 Replies
Contributor II

You can try combination of the AGGR and RANK functions.

Example:

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

Creator II
Author

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.

Creator II
Author

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.

Contributor III

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,

Creator II
Author

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

Contributor III

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

Creator II
Author

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'}>}Q1hours)+sum({< FM ={'MSL'}>}Q2hours)+sum({<FM ={'MSL'}>}Q3hours)+ sum({<FM ={'MSL'}>}Q4hours)

)

Thanks,

Niha

Contributor III

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,

Creator II
Author

It worked. you made my day!👌