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

[#2]

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.

Thank you for your help.

[#3]

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

[MK_QSL · Accepted solution]

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....

[#5]

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)

[MK_QSL]

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..