Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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,
First Thank you for your help
Probably very useful
But i don’t see your script ….
sorry, adding now
Thk yu
Very useful