Thanks for your reply. I am trying to describe the condition step by step.
Total dropqty party wise is as follows:
party sum(dropqty) (vDrop * sum(dropqty)) / 100 A 259 129.5 B 245 122.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:
Party Item Sales% dropqty A m7 17 45 A m2 12 32 A m10 30 30 B m2 32 56 B m8 54 34 B m3 16 32
Thanks & Regards,
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:
If(party=Previous(party), RangeSum(dropqty, peek('Cumulative')),dropqty) AS Cumulative,
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.
Thanks for your reply.
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).
Party Item Sales% dropqty A m1 10 40 B m6 10 23 B m7 8 8
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,
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.
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.
straight_cumm3.qvw 169.5 K
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,