7 Replies Latest reply: Nov 7, 2011 6:43 AM by Some Nath Roy

# Calculate maximum contributors

Hi All,

I have a table which has fields : party, item, sales% and dropqty. Two input variables named vSales and vDrop are to be used to filter the records.

Now I have to extracts those records from the table which will satisfy the following conditions :

condition 1 : sales%  must be greater than vSales value.

condition 2 : maximum contributors of the vDrop percentage of the total dropQty party wise.

If I consider a table like:

party, item, sales%, dropqty
A, m1, 10, 40
A, m2, 12, 32
A, m3, 15, 10
A, m4, 20, 23
A, m5, 15, 17
A, m6, 40, 18
A, m7, 17, 45
A, m8, 22, 19
A, m9, 16, 25
A, m10,30, 30
B, m1, 22, 10
B, m2, 32, 56
B, m3, 16, 32
B, m4, 40, 18
B, m5, 32, 22
B, m6, 10, 23
B, m7, 8, 8
B, m8, 54, 34
B, m9, 18, 25
B, m10,28, 17

The final output shold be like considering vSales = 10 and vDrop = 50:

PartyItemSales%dropqty
Am71745
Am21232
Am103030
Bm23256
Bm85434
Bm31632

A qvw file is attached herewith for ready reference. Looking for your help.

Thanks & Regards,

Somnath

• ###### Calculate maximum contributors

Hi Somnath,

could you explain

condition 2 : maximum contributors of the vDrop percentage of the total dropQty party wise.

a little more?

Regars,

Stefan

• ###### Re: Calculate maximum contributors

Dear Stefan,

Thanks for your reply. I am trying to describe the condition step by step.

Total dropqty party wise is as follows:

partysum(dropqty)(vDrop * sum(dropqty)) / 100
A259129.5
B245122.5

Now, after cosidering the condition 1 if create a table with dropqty in descending order and create a calculated column named Cumulative as follows:

 party item sales% dropqty Cumulative A m7 17 45 45 A m2 12 32 77 A m10 30 30 107 A m9 16 25 132 A m4 20 23 155 A m8 22 19 174 A m6 40 18 192 A m5 15 17 209 A m3 15 10 219 B m2 32 56 56 B m8 54 34 90 B m3 16 32 122 B m9 18 25 147 B m5 32 22 169 B m4 40 18 187 B m10 28 17 204 B m1 22 10 214

Now I would have to take those lines in the final result having 'Cumulative' value less than the [ (vDrop * sum(dropqty)) / 100] value of a party0. Thus the final result should be as follows:

PartyItemSales%dropqty
Am71745
Am21232
Am103030
Bm23256
Bm85434
Bm31632

Thanks & Regards,

Somnath

• ###### Re: Calculate maximum contributors

Dear Somnath,

I am fairly new the QV, but i tried to solve your problem as good as possible.

First I added a cumulative field in the scripting area, which makes it more convenient to not beeing forced to use a sorted table:

Data:

NoConcatenate

item,

sales%,

If(party=Previous(party), RangeSum(dropqty, peek('Cumulative')),dropqty) AS Cumulative,

dropqty

Resident Source order by party ASC,dropqty DESC;

Then you can just simply add a table and use the formula:

=if(sales%>\$(vSales),if(below(Cumulative<=(total_dropqty_party * \$(vDrop)) / 100,0),1,null()))

All items achieving your conditions will get marked by "1".

I guess it's not the best solution but it should solve the problem.

Regards

David

• ###### Re: Calculate maximum contributors

Dear David,

The calculation of the Cumulative field can not be done at the script level. If this is be done, some of the item's of a party not having sales% greater than vDrop value may contribute in the Cumulative dropqty. This is incorrect.

In our example case the following records should not contribute in the cumulative dropqty after considering

sales% > vSales (= 10).

PartyItemSales%dropqty
Am11040
Bm61023
Bm788

First we need to filter the table depending on the condition sales% greater than vSales. After that we need to find out maximum contributor items of a party in the vDrop percentage of the total dropqty.

Thanks & Regards,

Somnath

• ###### Re: Calculate maximum contributors

Dear Somnath,

Sorry for this mistake. You are right, on the script level should the conditions be considered. I added another if statement:

if(sales%>\$(vSales),If(party=Previous(party), RangeSum(dropqty, peek('Cumulative')),dropqty),peek('Cumulative')) AS Cumulative,

I hope this will fix the problem. Altough if you change the vSales% you will always have to re-run the script.

Regards

David

• ###### Re: Calculate maximum contributors

Hi Somnath,

if you don't want a script based solution, I could only offer you something like attached.

It's not really nice looking (besides, it could probably be simplified, but I can't spend more time on this).

As you will see, I filterers successfully the rows you are interested in. But since we use chart inter record functions, the supress when zero will not work, so there are still a lot of unnecessary empty rows shown.

I tried with aggr function and I think I could get it to work if aggr() would allow to sort the dimensions (I need dropyqty sorted desc, which is I think is not possible). I tried for a short time with manipulating load order (I thought that aggr() would care for that), but with no success).

If you are interested, it would maybe look like this:

=aggr( if(rangesum(top( dropqty,1,RowNo()))<=\$(vDrop)*sum(total<party> dropqty)/100, dropqty) ,party,dropqty)

(not the full solution incl. condition1, but even only condition2 is not correct in this case)

All in all, not very satisfying, I also searched the forum and it seems that others had problems here too.

Regards,

Stefan

• ###### Re: Calculate maximum contributors

Dear Stefan,

Thanks a lot for your help.

I experienced the same thing that aggr() function is not working with the sort order of the dropqty.

From the calculation point of view your solution is absolutely fine.

Still I am expecting a better approach if we can supress null even we use inter record functions.

Thanks & Regards,

Somnath