Positive/Negative filter in a bar chart

[Loreen]

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

[marcus_sommer]

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

[Loreen · Accepted solution]

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

[marcus_sommer]

It might not be relevant in your scenario but just to check for 1 neglects those cases which have sum(%Result) = 0.

- Marcus