Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
You can try combination of the AGGR and RANK functions.
Example:
=AGGR(IF(Rank(SUM(Equation))<=20 ,SUM(Equation)), Region)
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.
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.
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,
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)
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
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
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,
It worked. you made my day!👌