15 Replies Latest reply: May 2, 2014 2:27 AM by Manish Kachhia

# Sum of Top numbers calculated in expressions

Hello All,

I have a column "Cost" using calculated numbers from "Expressions". In the table, I have more than 100 rows, while I want to show only top 20 of them and also the sum in the first row.

I know the sum of top N expression. But in that case, in the expression,  I can only use available fields but not calculated fields, right?

If in the "Presentation", I pick "Max Number" as 20, it will give me top 20 rows, but the sum is stil the total of all the rows.

Then how do I do that then?  Thank you

• ###### Re: Sum of Top numbers calculated in expressions

Modify your dimension to below and select "Suppress when value is null"

=If(Aggr(Rank(Your_expression) <= 20,Dim),Dim)

• ###### Re: Sum of Top numbers calculated in expressions

Hi Sindy,

Try something like this Sum({<You_DIMENSION= {"=Rank(SUM(Cost))<=20"}>}Cost)

Or share a small example of your application

• ###### Re: Sum of Top numbers calculated in expressions

Hi, not sure how I can upload a file but here's what I have as a small example:

Here's the data I load :

Item     Extra Case     Extra handling fee/ case

Then I created a straight table with "Item" and "Extra Case" as dimensions, and in the Expressions, I have created new label "Total extra handling fee" with calculation "[Extra Case]*[Extra handling fee/case]".

Then, the straight table has 3 columns : Item, Extra Case, Total extra handling fee. So my question is, how do I show only top 5 items per total extra handling fee, and also the sum of the top 5? Since I already have the expression for the Total extra handling fee, the aggr function is going to overwrite my calculation for total fee?

Hope I explain my question well. Thanks

• ###### Re: Re: Sum of Top numbers calculated in expressions

If I catch your idea, you can use dimension limit

PFA an example

• ###### Re: Re: Sum of Top numbers calculated in expressions

Dimension limits can only apply to the first expression right? In the small example, Total fee is the first expression. However, in my real case, it's not.

• ###### Re: Re: Sum of Top numbers calculated in expressions

Sindy Wang wrote:

Dimension limits can only apply to the first expression right? In the small example, Total fee is the first expression. However, in my real case, it's not.

No. You can change the order of columns and it doesn't affect to results.

As you can see in example I set dim limit for both dimensions.

Right Click\Properties\Dimension Limits

• ###### Re: Re: Sum of Top numbers calculated in expressions

Hi Sergey,

Yours is bar chart. I am not sure how I can do that in straight table.

I have attached a small example in Manish's reply. Can you see if you can help? Thanks

• ###### Re: Sum of Top numbers calculated in expressions

Hello,

You can attach a sample by using "Use advanced editor" option.

You can also get further assistance from this doc: Preparing examples for Upload - Reduction and Data Scrambling

Thanks,

• ###### Re: Sum of Top numbers calculated in expressions

Below is the alternative idea.

=aggr(if(rank(total sum(Cost))<=20,Dimension),Dimension)

• ###### Re: Sum of Top numbers calculated in expressions

It is not clear from your question that which TOP 20 numbers you want to show.

i.e. Based on Rank or Based on Script Load (First 20)

You can use Calculated Dimension to get the desired result but accurate help can be given if you provide sample data file along with your desired result... rather than we just guess what you want !!

Thanks

• ###### Re: Re: Sum of Top numbers calculated in expressions

This is the small example.

I wanted to show only top 5 of total cost, and also the sum at the first row. As you can see the total cost already has an expression with it.

And sorry for confusion of my account. Something wrong with my old account "sindy" so I have to create new one.

• ###### Re: Sum of Top numbers calculated in expressions

Create a Straight Table

Dimension

1) Calculated Dimension

=IF(Aggr(Rank(SUM([Extra Cases]*([Handling cost 1 per extra case]+[Handling cost 2 per extra case])),4),Item)<=5,Item)

2) [Extra Cases]

Tick Suppress When Value is Null

Expression

1) Cost2

[Extra Cases]*[Handling cost 2 per extra case]

2) Cost1

[Extra Cases]*[Handling cost 1 per extra case]

3) Total Cost

[Extra Cases]*([Handling cost 1 per extra case]+[Handling cost 2 per extra case])

In Total Mode for all expression : Select Sum of ROWS

Hope this helps....

• ###### Re: Sum of Top numbers calculated in expressions

Manish, this works! It's awesome! Thank you very much!

Also, if for each item, I have different total cost because "extra cases" is different, then the result will be top 5 items with top total of (total cost) of that item, is that correct?

ex.

Item     Extra Cases     Total cost

1          5                         ...

1          8                         ...

2          3                          ...

2          5                           ...

2          9                           ...

BTW, is it possible for you to break down the formula and kind explain what it means?

=IF(Aggr(Rank(SUM([Extra Cases]*([Handling cost 1 per extra case]+[Handling cost 2 per extra case])),4),Item)<=5,Item)

• ###### Re: Sum of Top numbers calculated in expressions

I hope you have marked Suppress When Value is NULL for Both Dimensions in Dimension Tab

=IF(Aggr(Rank(SUM([Extra Cases]*([Handling cost 1 per extra case]+[Handling cost 2 per extra case])),4),Item)<=5,Item)

Let me break this, so that easy to understand

1) SUM([Extra Cases]*([Handling cost 1 per extra case]+[Handling cost 2 per extra case]

Same as your below expression. Just changed mathematically

SUM([Extra Cases]*([Handling cost 1 per extra case]

+

SUM([Extra Cases][Handling cost 2 per extra case]

Consider that above formula is Result1

2) Rank(Result1,4)

We can simply take Rank(Result1) but when there are more than two sum is equal, it will give you result of Rank as 2-3, or 4-5 etc. Anyway this is not important here as you are not looking for Rank to be displayed.

So you can use either or Rank(Result1,4) or Rank(Result1)

3)Aggr(Rank(Result1,4),Item)

In calculated Dimension, we are looking for TOP Items, so the above formula has to be aggregated to Item

4) IF(Aggr(Rank(Result1,4),Item)<=5,Item)

Now using IF, we are checking which are the items are having Rank less than or equal to 5 and displaying the same.

Here If will give you two results

a) Items having Top 5 ranks

b) items which are not having top 5 ranks. For them we haven't displayed any thing so they will appear as - or

null and hence we ticked Suppress When Value is NULL

Hope this helps..