topic Re: Calculate maximum contributors in QlikView

Calculate maximum contributors

somenathroy — Thu, 03 Nov 2011 12:16:35 GMT

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:

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

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

Thanks & Regards,

Somnath

Calculate maximum contributors

swuehl — Thu, 03 Nov 2011 12:22:52 GMT

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

somenathroy — Thu, 03 Nov 2011 13:26:39 GMT

Dear Stefan,

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,

Somnath

Re: Calculate maximum contributors

Thu, 03 Nov 2011 15:26:24 GMT

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

LOAD party,

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

somenathroy — Fri, 04 Nov 2011 04:54:14 GMT

Dear David,

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,

Somnath

Re: Calculate maximum contributors

Fri, 04 Nov 2011 07:57:55 GMT

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

swuehl — Fri, 04 Nov 2011 14:15:55 GMT

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

somenathroy — Mon, 07 Nov 2011 11:43:43 GMT

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