topic Re: Problem with Sign function in QlikView

Problem with Sign function

Loreen — Fri, 15 Feb 2019 15:36:25 GMT

Hi everybody,

I create 2 calculated dimensions =Sign(Aggr(Sum(%PNV),[OrderPost_id])) and =if(Sign(Aggr(Sum(%RBC),[OrderPost_id]))<>1,'Negative RBC','Positive RBC').

The calculated dimension with RBC works for all the data except when the PNV is negative, it gives me this :

I don't understand why there's a '1' for negative numbers with PNV calculated dimension. Besides, as you can see, it shows 'Negative RBC' for only 2 rows but not for the others with RBC calculated dimension.

Do you have an idea of what could be the reason of it ?

Thanks in advance

Regards

Loreen

Re: Problem with Sign function

pradosh_thakur — Fri, 15 Feb 2019 15:47:59 GMT

=Sign(Aggr(Sum(%PNV),[OrderPost_id]))

=if(Sign(Aggr(Sum(%RBC),[OrderPost_id]))<>1,'Negative RBC','Positive RBC').

I dont think %PNV and %RBC are same . Are they ?

Re: Problem with Sign function

pradosh_thakur — Fri, 15 Feb 2019 15:49:09 GMT

May be try using fabs() and see if you encounter the problem or not.

Re: Problem with Sign function

marcus_sommer — Fri, 15 Feb 2019 15:58:35 GMT

Maybe the context of your calculation is wrong - means you might need a different and/or further dimensions within the aggr().

- Marcus

Re: Problem with Sign function

sunny_talwar — Fri, 15 Feb 2019 16:08:41 GMT

What is PNV column here a calculated dimension? a dimension? or a measure? If it is not a straight field... can you share the expression behind it?

Re: Problem with Sign function

Loreen — Mon, 18 Feb 2019 13:50:05 GMT

No, PNV and RBC are different measures

Re: Problem with Sign function

Loreen — Mon, 18 Feb 2019 15:40:01 GMT

Here are the data that I obtain with the calculated dimensions (last 2 columns) :
The result that I'm looking for is that the column RBC and the column with the calculated dimension =Aggr(sum(%RBC),OrderPost_id) show the same number. It works for every positive number, but not with negative ones. I don't understand how these amounts are calculated. I tried to change the dimension in the aggr function but it doesn't seem to work.