
Re: Sum of Top numbers calculated in expressions
anbu cheliyan Apr 29, 2014 9:32 PM (in response to Linda Huang)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
Sergey Makushinsky Apr 29, 2014 9:33 PM (in response to Linda Huang)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
Linda Huang Apr 30, 2014 1:28 AM (in response to Sergey Makushinsky )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
Sergey Makushinsky Apr 30, 2014 1:35 AM (in response to Linda Huang)If I catch your idea, you can use dimension limit
PFA an example

1.qvw 153.0 K

Re: Re: Sum of Top numbers calculated in expressions
Linda Huang Apr 30, 2014 2:00 AM (in response to Sergey Makushinsky )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
Sergey Makushinsky Apr 30, 2014 2:14 AM (in response to Linda Huang)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
Linda Huang Apr 30, 2014 12:41 PM (in response to Sergey Makushinsky )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
Angad Singh Apr 30, 2014 2:34 AM (in response to Linda Huang)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



Re: Sum of Top numbers calculated in expressions
Junya Fujiyoshi Apr 29, 2014 11:54 PM (in response to Linda Huang)Below is the alternative idea.
=aggr(if(rank(total sum(Cost))<=20,Dimension),Dimension)

Re: Sum of Top numbers calculated in expressions
Manish Kachhia Apr 30, 2014 2:10 AM (in response to Linda Huang)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
Linda Huang Apr 30, 2014 12:39 PM (in response to Manish Kachhia )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.

Sum_Top_430.qvw 153.0 K

Re: Sum of Top numbers calculated in expressions
Manish Kachhia Apr 30, 2014 12:46 PM (in response to Linda Huang)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
Linda Huang May 1, 2014 10:27 PM (in response to Manish Kachhia )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
Manish Kachhia May 2, 2014 2:27 AM (in response to Linda Huang)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 23, or 45 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..



