Netting ... How to collapse / expand in pivot table base on condition

[zarmoton]

Hello

Not easy !!  please can you help me on this complex (for me !!) problem :

i have the following data

id	Zone	Way	Qty	price
1000	A	1	100	20
1001	A	1	50	21
1002	A	-1	20	28
1003	B	-1	200	50
1004	B	1	200	55
1005	C	1	30	20
1006	C	1	70	20
1007	C	-1	100	20
1008	D	1	50	25
1009	D	-1	75	12

on the simple pivot table we can have

on a simple pivot table we have
	Qty_Net	Price_Net
	sum(way*qty)	sum(way*qty*price)
A	130	2490
B	0	1000
C	0	0
D	-25	350

and i would like to be able to see:

Zone	id	Qty_Net	Price_Net_avg
A	0	130	19.15		<-- Grouping because  Qty_net > 0 and price_net > 0
B	1003	-200	50		<-- No grouping, because qty_net = 0 (see in B20)
B	1004	200	55
C	0	0	0		<-- No detail because Qty_net =0 and Price_net = 0
D	1008	50	25		<-- No grouping, because price_net / qty_net <  0 (350/-25)
D	1009	-75	12

My problem is how to collapse and do sum depending on condition

thank you for your help.

Warm Regards

[#2 · Accepted solution]

Laurent,

I usually do this with scripting and setting flags versus complex expressions.

Is this what you are looking for?  I have attached sample app with script.  Let me know if this helps

[zarmoton]

First Thank you for your help

Probably very useful

But i don’t see your script ….

Can you attach your qvw ?

thk you again

Regards,

[zarmoton]

First Thank you for your help

Probably very useful

But i don’t see your script ….

[#5]

sorry, adding now

[zarmoton]

Thk yu

Very useful