Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
<body><p>Following table is given:</p> <p> </p> <p>Customer | Article | </span>Sales</p> <p>----------------------------------------------------</p> <p>C1 | A1 | 4</p> <p>C1 | </span>A1 | </span>7</p> <p>C1 | </span>A1 | </span>-10</p> <p>C1 | </span>A2 | </span>2</p> <p>C1 | </span>A2 | </span>5</p> <p>C1 | </span>A2 | </span>3</p> <p>C1 | </span>A3 | </span>-4</p> <p>C1 | </span>A3 | </span>6</p> <p>C1 | </span>A3 | </span>-5</p> <p>C2 | </span>A1 | </span>-1</p> <p>C2 | </span>A1 | </span>3</p> <p>C2 | </span>A1 | </span>-4</p> <p>C2 | </span>A2 | </span>7</p> <p>C2 | </span>A2 | </span>-9</p> <p>C2 | </span>A2 | </span>5</p> <p>C2 | </span>A3 | </span>1</p> <p>C2 | </span>A3 | </span>0</p> <p>C2 | </span>A3 | </span>-3</p> <p> <tbody> </tbody> </p> <p> </p> <p>Desired table have to show the sales per customer but only regarding article which have negative total of sales for the customer</p> <p> </p> <p>Customer | </span>Sales | </span>Comment</p> <p>-----------------------------------------------------------------------------</p> <p>C1 | </span>-3 | </span>only article A3: -4 + 6 + (-5)</p> <p>C2 | </span>-4 | </span>only article A1 and A3: -1 + 3 + (-4) + 1 + 0 + (-3)</p> <p> </p> <p>How can I code it into a formula?</p> <p> </p></body>
sum(aggr(if(sum(Sales)<0,sum(Sales)),Customer,Article))
See attached for the sequence of steps I went through to get there, which also kind of explains how and why it works.
Edit: Simpler expression.
sum(aggr(rangemin(0,sum(Sales)),Customer,Article))
View solution in original post
Thank you John, Thats it !!!
Best regards
ra117hama (Anton)
