Solved: Sum of all largest values - Qlik Community

dzmitry_shmurye · ‎2016-04-20

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!

sunny_talwar · ‎2016-04-22

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)

View solution in original post

tamilarasu · ‎2016-04-20

Try,

Sum({<Value={">5"}>}Total Value)

dzmitry_shmurye · ‎2016-04-20

But I need do this for every row in table.

For example for the 1 rows it should be sum values only wich > 10 etc...

ThornOfCrowns · ‎2016-04-20

When you say 'greater than current string' what do you mean by 'current string'?

dzmitry_shmurye · ‎2016-04-20

Yes, exactly!

MindaugasBacius · ‎2016-04-20

Two cases:

Result as the text object.
Result as a table object with reduced data.

dzmitry_shmurye · ‎2016-04-20

I need get the next values (different for every row):

sunny_talwar · ‎2016-04-20

May be this?

You will have to keep the sorting like this to get this to work in the solution I am proposing

Sum(Total Value) - RangeSum(Above(Sum(Value),0, RowNo()))

NOTE: Used Mindaugas Bacius‌ sample to make modification as I was too lazy to create my own

kkkumar82 · ‎2016-04-20

Hi Sunny

Just curious can we try this edit script using a loop kind of thing

kkkumar82 · ‎2016-04-20

For acheiving this does it require to sort the products in order , what I observed