Sum of all largest values

[dzmitry_shmurye]

Hi community!

I have a small table:

The first expresion is the simple sum.

I want get the second expresion with sum of the all values in the table but only values wich are largest than current string.

For example for Prod2 Sum 10+15+20 = 45 (all values >5, but not include 1)

Could somebody help me with this expresion?

Thanks!

[dzmitry_shmurye]

I created the small example of my table (attached).

I need get the values that in the text objects, but i want calculate this values using onky expresion (or may be a bit data transformation by simple script).

Is it possible to get the needed values for this example using only expresion?

Thanks!

[sunny_talwar · Accepted solution]

Script:

InitialData:

LOAD *,

  AutoNumber(Month1&Product1) as Key1;

LOAD Month(Date#(Month1, 'MMM')) as Month1,

  Product1,

  Amount1

Inline [

Month1, Product1, Amount1

Jan,  Prod1,  5

Jan,  Prod2,  10

Jan,  Prod3,  12

Feb,  Prod1,  1

Feb,  Prod2,  5

Feb,  Prod3,  3

Mar,  Prod1,  20

Mar,  Prod2,  14

Mar,  Prod3,  7

];

FinalTable:

LOAD Month1 as Month,

  Product1 as Product,

  Amount1 as Amount,

  Key1 as Key

Resident InitialData

Order By Month1, Amount1;

Expression:

=Sum(TOTAL <Month> Amount) - Aggr(RangeSum(Above(Sum(Amount), 0, RowNo())), Month, Key)

[dzmitry_shmurye]

Thank you Sunny!!!

[dzmitry_shmurye]

I have good idea to use the new functionality of the Aggr() function:

Aggr({SetExpression}[DISTINCT] [NODISTINCT ] expr, StructuredParameter{, StructuredParameter})

The sortable Aggr function is finally here!

Could i write the expression something like this?

=Sum(TOTAL <Month> Amount) - Aggr(RangeSum(Above(Sum(Amount), 0, RowNo())), Month, (Key, (NUMERIC, ASCENDING)) )

and remove from the script all data modification?

[sunny_talwar]

I don't think this is going to work, but you can give it a try.

[dzmitry_shmurye]

Don't work correct for two dimensions(

Any ideas how to use this StructuredParameter?

[sunny_talwar]

Can you share this document?

[dzmitry_shmurye]

Yes, attached.

[sunny_talwar]

See if this is what you wanted

[dzmitry_shmurye]

Thans Sunny!

I try  to write the next expresion and it works correctly and without Key creating:

=Sum(TOTAL <Month> Amount) - Aggr(RangeSum(Above(Sum(Amount), 0, RowNo())), Month,(Amount,(NUMERIC, ASCENDING)) )