Hi All,
In dimensional function while using toppercent function,
TopPercent(Set(A,b,c,d,e,f),50,tuple([fy2014],[Qty])
Out of these five members a,b,c,d,e,f, it will give us the top 50 percent of what ..? for example consider below data and how will the output be calculated. I am not clear on the percent calculation.
Memeber tuple([fy2014],Qty)
A 67
B 4343
C 33
D 333
E 223
F 4349
The above query will evaluate to it..but how is the 50 percent calaculated and on what... I knew it will order all the members based on the last parameter value and then
so above it would be (F,B,D,E,A,C).... now after this ?
Thanks,
Nithya
topPercent ( set_expression , percentage , numeric_expression2 )
Sorts the set specified in "set_expression" in descending order and returns the topmost elements from the sorted set whose cumulative percentage of the total is greater than or equal to "percentage".
Example: topPercent ( set ( [Camping Equipment] , [Golf Equipment] , [Mountaineering Equipment] ) , 40 , [2006] )
Result: For the set of Camping Equipment, Golf Equipment, and Mountaineering Equipment, returns the members with the largest Gross profit whose total for the year 2006 is at least 40% of the overall total.
Quote from: nithya1224 on 12 Sep 2014 02:06:04 PM
Hi All,
In dimensional function while using toppercent function,
TopPercent(Set(A,b,c,d,e,f),50,tuple([fy2014],[Qty])
Out of these five members a,b,c,d,e,f, it will give us the top 50 percent of what ..? for example consider below data and how will the output be calculated. I am not clear on the percent calculation.
Memeber tuple([fy2014],Qty)
A 67
B 4343
C 33
D 333
E 223
F 4349
The above query will evaluate to it..but how is the 50 percent calaculated and on what... I knew it will order all the members based on the last parameter value and then
so above it would be (F,B,D,E,A,C).... now after this ?
Thanks,
Nithya
The total for all of these members is 9348. Therefore 50% of this is 4674. This is the threshold the report will use when accumulating the measure values for the members.
Member | Measure | Accumulated total | Threshold met | Included in the report |
F | 4349 | 4349 | N | Y |
B | 4343 | 8692 | Y | Y |
D | 333 | 9025 | Y | N |
E | 223 | 9248 | Y | N |
A | 67 | 9315 | Y | N |
C | 33 | 9348 | Y | N |
The first member will always be included. Since it's accumulated total is not greater than 50% of the overall total, the next member will also be included (the threshold has not yet been met).
The second member is included and takes the accumulated total to 8692. Since this is more than 50% of the overall total, no further members are included.
Cheers!
MF.
why do we need to include second value as its accumulated value is greater than 50 % of overall total.
Thanks,
Nithya
Quote from: nithya1224 on 30 Sep 2014 09:42:39 AM
why do we need to include second value as its accumulated value is greater than 50 % of overall total.
Thanks,
Nithya
Because the first value alone does not meet the threshold of 50%. If you specified 40 as the percentage, the first value alone would exceed this, so you would only see member F.
MF.