Bottom 5 sales

[#1]

Hello ,

i am using this expression to find comparison between Actual Margin and Quoted Margin sorted for  5 highest revenue  giving account using this expression

If(Aggr(Rank(SUM(Revenue),4),Account)<=5,Account), however i am trying to calculate same thing for  bottom 5 revenue ,

please suggest

[Anonymous]

Hi,

Try:

One way to derive bottom 5 records,the aggregate value need to multiplied by -1.

=aggr(If(RANK(aggr(-1 * sum(Revenue),  Account)) <= 5, Account), Account )

or

If(Aggr(Rank(-SUM(Revenue),4),Account)<=5,Account)

or

=aggr(if(rank(-sum(Revenue))<=5,Account),Account)

Regards

Neetha

[#3]

Hi, Neetha

i tried both expression but its  not working in my case , for you i have attached sample excel file and a qvw file, kindly review and suggest.

[jzimolong]

I can't open the .qvw, can you provide me with the dimensions and expressions?  Looking at your question, have you experimented with dimension limits, using the "Show Only" smallest 5?

[nikhilgarg]

HEy,

I think if you have 20 records then you want last five records. Right ??

In that case you should use following expr:

If(Aggr(Rank(SUM(Revenue),4),Account)>=15,Account)`

I think it should work

[#6]

I am using Calculated  Dimension "If(Aggr(Rank(-SUM(Revenue),4),AccountName)>5,AccountName)" and using expression to calculate actual margin and quoted margin

(SUM(Revenue) - SUM(Cost))/SUM(Revenue) -for actual margin

(SUM(QuotedPrice) - SUM(QuotedCost)) / (SUM(QuotedPrice))-for quoted margin

i am trying to show comparison between actual margin and quoted margin of five bottom most revenue generating project. if i use dimension limits it will restrict accordingly depending on first expression which is expression calculate actual margin.

[#7]

I am using Calculated  Dimension "If(Aggr(Rank(-SUM(Revenue),4),AccountName)>5,AccountName)" and using expression to calculate actual margin and quoted margin

(SUM(Revenue) - SUM(Cost))/SUM(Revenue) -for actual margin

(SUM(QuotedPrice) - SUM(QuotedCost)) / (SUM(QuotedPrice))-for quoted margin

i am trying to show comparison between actual margin and quoted margin of five bottom most revenue generating project. if i use dimension limits it will restrict accordingly depending on first expression which is expression calculate actual margin.

the expression that you suggested will give all 15 records

[nikhilgarg]

Are you sure that my following expression will give all 15 records ?

If(Aggr(Rank(SUM(Revenue),4),Account)>=15,Account)

Because it will give only records having ranks either equal to greater than 15.

thanks

[kavita25]

Hi,

According to your excel sheet Bottom 5 Account Name should be :

B	$1,880.00
A	$1,500.00
D	$1,200.00
C	$930.00
C	$358.30

Right??

Regards,

Kavita

[Anonymous]

Hi Prashant,

Check attached files.

Please check is it  as per your requirement

Regards

Neetha