Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone, I have an issue, maybe you could help me.
My purpose is to identify orders with negative results. Orders may include multiple positions. In an order, results of the different positions counterbalance.
Example 1 :
Order Number Position Result
001 01 150€
001 02 -30€
Result of order 001 = 120€
Example 2 :
Order Number Position Result
002 01 -360€
002 02 50€
Result of order 002 = -310€
I need to identify all orders which have a negative total result, like the second example.
When I create a straight table with this expression =if($(v_RBC)<0,$(v_RBC),Null()) , it works, it shows the orders with a negative total results, and for the others, it shows a zero. But I want to display it in a bar chart and when I use the same expression, I have the “no data to display message”.
I tried it another way. I use the RBC expression $(v_RBC) and I create a filter with a calculated dimension: =if(Sign(%RBC)=1,'Positive RBC','Negative RBC'). But with this filter, when I choose Negative RBC, it does not just take into account negative total results by orders, but negative result from every position order.
So if I go back to previous example, instead of not giving me any negative result for order 001, it shows -30€ for order 001.
What can I do to display only negative total result by order in a bar chart ?
I hope it’s clear enough
Thank you in advance for your reply
Hi,
I finally find a solution thanks to your help. I create this calculated dimension :
=if(Sign(aggr(sum(%Result),[OrderPost_id]))=1,'Positive','Negative')
Thank you for your help
Regards
Loreen
As calculated dimension you could use something like this:
pick(sign(aggr(sum(Result), [Order Number])) + 2, 'negative', 'balanced', 'positive')
but depending on your requirements it could be more useful to do this kind of aggregation within the script.
- Marcus
Hi,
I finally find a solution thanks to your help. I create this calculated dimension :
=if(Sign(aggr(sum(%Result),[OrderPost_id]))=1,'Positive','Negative')
Thank you for your help
Regards
Loreen
It might not be relevant in your scenario but just to check for 1 neglects those cases which have sum(%Result) = 0.
- Marcus