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]

Thanks Sunny!

It is possible do this without sorting?

Because i need also do this feature in pivot table when 1 dimension in columns?

[sunny_talwar]

Yes sorting is important for this to work

[sunny_talwar]

Script can work, but will require aggregation (Sum(Value)) in the script. If that is acceptable, it can def. work

[kkkumar82]

Tried with this logic in edit script can you tweek it

Data:

Load * Inline [

Prod, Value

Prod1,10

Prod2,5

Prod3,15

Prod4,1

Prod5,20

];

Let vcount = FieldValueCount('Value');

for i = 1 to $(vcount)

Let vvalue = FieldValue('Value',$(i));

Load Prod,

     Sum(Value) as Newvalue

     Resident Data

     where Value > $(vvalue)

     group by Prod;

     Next 

[sunny_talwar]

More like this may be:

tmp:

LOAD * INLINE [

    Prod,Value

    Prod1, 10

    Prod2,5

    Prod3,15

    Prod4,1

    Prod5,20

];

FinalTable:

LOAD *,

  CumValue - Value as ReqValue;

LOAD *,

  RangeSum(Value, Peek('CumValue')) as CumValue

Resident tmp

Order By Value desc;

DROP Table tmp;

[kkkumar82]

Great sunny your are a master of a special kind, feeling great you being in the community,

Hats off

[sunny_talwar]

Script option isn't feasible? Another option is to use Aggr(), but would require intervention in script.

[dzmitry_shmurye]

Yes, I can't use script for this

I want create the pivit table with the month dimension in ccolumns and prod dimension in rows and for every month I want calculate this sum for every product rows.

Could you please helm me with the expresion withh Aggr() (by every month in colums)?

Thanks!

[sunny_talwar]

Here is the issue, that we need the correct sorting in the script for Prod based on ascending Sum(Value)

tmp:

LOAD * INLINE [

    Prod1,Value, Dim

    Prod1,4, A

    Prod1,6, B

    Prod2,3, A

    Prod2,2, B

    Prod3,10, A

    Prod3,5, B

    Prod4,1, B

    Prod5,4, A

  Prod5,16, B

];

Table:

LOAD Prod1,

  Sum(Value) as Sum

Resident tmp

Group By Prod1;

Sort:

LOAD Prod1 as Prod

Resident Table

Order By Sum;

DROP Table Table;

FinalTable:

LOAD Prod1 as Prod,

  Value,

  Dim

Resident tmp;

DROP Tables tmp, Sort;

[sunny_talwar]

Would you be able to provide some more data with month information to work on that