QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

Tags (3)
1 Solution

Accepted Solutions
MVP

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

14 Replies
Honored Contributor III

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)

Valued Contributor

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

Not applicable

Re: Sum of Top numbers calculated in expressions

Below is the alternative idea.

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

Not applicable

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

Valued Contributor

Re: Re: Sum of Top numbers calculated in expressions

If I catch your idea, you can use dimension limit

PFA an example

Not applicable

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.

MVP

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

Valued Contributor

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

Not applicable

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,