AGGR TOP 2 users site wise in a pivot table

[Anonymous]

Hi,

Table Pivot:

Dimension:Region,Sitename

Expression:

= Concat( IF(aggr(rank(Sum(acctinputoctets)),LOGIN_ID)<3,LOGIN_ID&':'&aggr(num(Sum(acctinputoctets/1024)/1024,'#,##0'),LOGIN_ID)),',')

above is my expression to show top 2 users name and their downloaded data in MB site wise.

But only when i am selecting any particular site name , the top 2 users values are visible in the expression else it is showing blank cell.

I suppose this may be due to if condition at start and the expression needs to be converted into a aggregated expression then only values will be displayed without any site name selection.

Can anyone please help me on this?

Regards,

JATIN KOLHE.

[tresesco]

Try including the dimensions in the aggr(), like:

= Concat( IF(aggr(rank(Sum(acctinputoctets)),LOGIN_ID,Region,Sitename)<3,LOGIN_ID&':'&aggr(num(Sum(acctinputoctets/1024)/1024,'#,##0'),LOGIN_ID,Region,Sitename)),',')

[Anonymous]

Hi Tresesco,

This gives me output without any site selection.

But it gives all users list separated by comma's for a site instead of only top 2.

[MK_QSL]

Provide little sample data please..

[Anonymous]

Hi,

please find attached sample data.