topic Positive/Negative filter in a bar chart in QlikView

Positive/Negative filter in a bar chart

Loreen — Wed, 13 Feb 2019 14:13:31 GMT

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

Re: Positive/Negative filter in a bar chart

marcus_sommer — Wed, 13 Feb 2019 15:07:30 GMT

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

Re: Positive/Negative filter in a bar chart

Loreen — Thu, 14 Feb 2019 10:48:43 GMT

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

Re: Positive/Negative filter in a bar chart

marcus_sommer — Thu, 14 Feb 2019 11:50:08 GMT

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

- Marcus