Aggr

Both are same:

Aggr( {<SetExpression>} Sum(X)/Sum(Y), Dim)

Aggr( Sum({<SetExpression>} X)/Sum({<SetExpression>} Y), Dim)

max(aggr(sum(Members)-above(Sum(Members)), (MonthYear,(NUMERIC, ASCENDING))))

  • Aggr( {<SetExpressionA>} Sum({<SetExpressionB>} X)/Sum(Y), Dim)
  • Aggr( Sum({<SetExpressionA>*<SetExpressionB>} X)/Sum({<SetExpressionA>} Y), Dim)

=If(GetSelectedCount(Client) = 1, Concat(Aggr(Rank(Sum({<Client = >} Sales)), Client), ‘,’), ‘Select a Single Client’)

=aggr(rank(TOTAL(sum({<Client=>}Sales))), Client)

 

 

sum({<Monthserial={$(=max(Monthserial))},                                 customer={“=sum({<Monthserial={‘>=$(=max(Monthserial)-5)<$(=max(Monthserial))’}>}Sales)=0”}>}
Sales)

 

sum({<Customer={“=sum(Sales)>=$(=max(aggr(sum(Sales),Customer),4))”}>}Sales)

=Aggr(If(Sum(Sales)>=Max(TOTAL Aggr(Sum(Sales), Customer), 4), Customer), Customer)

If(Aggr(Rank(Sum(Sales)),Customer)<=10,’A’,’B’)

concat({<Bowler = {‘=rank(sum(Wickets))=1’}>}distinct Bowler, ‘,’)

=AGGR(IF(ONLY(AGGR( NODISTINCT COUNT(DISTINCT Name), Project))=1, ‘Confidential’, Name), Project, Name)

 

If your Currency in dimension, try like:

=FirstSortedValue(Aggr(Sum(rates),Date), -Date)   // If multiple rates are there for a date

=FirstSortedValue(rates, -Date)                               // when sigle rate for a date

Edit: The first exp, probably should be like: =FirstSortedValue(Aggr(Sum(rates),Currency,Date), -Date)

=Aggr(NODISTINCT Rank(Sum(amount)),Person)

=aggr(if(rank(Random)   <=3,Person_ID),Person_ID)
=aggr(if(rank(Timestamp)<=5,Timestamp),Person_ID,Timestamp)

Sum(Aggr(FirstSortedValue(Amount,-Aggr(Max(TransDate),Prod,Cust,TransDate)),Prod,Cust))

 

Create a Pivot Table

Dimension

1) Year

2) Calculated Dimension

=IF(Aggr(Rank(SUM(Premium),4),Year,Claim No])<=3 or Aggr(Rank(-SUM(Premium),4),Year,[Claim No])<=3,[Claim No])

For both Dimensions Tick Suppress When Value is NULL

Expression

SUM({<Year = {‘$(=Year(Today()))’}>}Premium)

 

IF(DIMENSIONALITY()=1 OR DIMENSIONALITY()=2, sum(aggr(sum(Aggr(avg(SALES),STATE)),COUNTRY,REGION)),IF(DIMENSIONALITY()=3,sum(Aggr(avg(SALES),STATE)),sum(Aggr(Avg(SALES),CITY))))

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.