How filter all records which contribute nearly 95% of total sum?

[pawe84]

Hi everyone,

I have a table with sales data like this, the table is already ordered by sales desc:

Now I only need the invoice numbers which contribute nearly 95% of total sales.

In this example total sales=7927 , 95% sales = 7530.65

Now we can see that only the sum of invoice number 101 and 100 (7400) are very close to 7530.65.

If I have several thousand records how I can filter those qualified records?

Do I have to use modules like t_aggregate and t_sort?

Thanks in advance for any hints.

[TRF]

If the objective is to get the n high sales to reach the target of 95% of the total sales, here is a simple solution:

Bith tFixedFlowInput are here to produce the sample data set.

tJava and tLogRow are here to display target (95% of all sales) and final result.

With 1rst subjob, compute the total sales with tAggregatRow then the target stored into a global variable:

 

For 2nd subjob, order rows based on the sales field in descending order then in tMap, add each sales value as soon as the result is less or equal to the target (else, ignore the current row and get the next one and so on):

Here is the result:

If the objective is to get the better solution to reach 95% of the sales value, this is a complete different story as you have to try all possible combinations...

[pawe84]

Hi thank's,

this solution is nearly perfect.

But I don't need the invoiceno 105, because after sales has been sorted descending and it should check only 

if the current sum >= target sum depending of the order.

In this case  invoiceno 103 would be the next but the sum (sales) of 101+100+103 > 7530.65

That means only 101 and 100 are qualified.

I think I have to modify the variable currentSum, isn't it?

[TRF · Accepted solution]

Do you want to stop the process as soon as a row hits the target?

If so, change the expression for the currentSum variable to:

Var.currentSum + row5.sales

Don't change anything else.

It should work. 

[pawe84]

Thanks, that's exacly what I was looking for.

[TRF]

Great!

Don't forget to mark your case as solved (Kudos also accepted as a bonus).