Sum of Top numbers calculated in expressions

[#1]

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

[anbu1984]

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

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

[SergeyMak]

Hi Sindy,

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

Or share a small example of your application

[#4]

Below is the alternative idea.

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

[#5]

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

[SergeyMak]

If I catch your idea, you can use dimension limit

PFA an example

[#7]

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.

[MK_QSL]

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

[SergeyMak]

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

[#10]

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,

Angad