topic positives values in QlikView

positives values

sunnybi — Tue, 18 Feb 2025 12:40:08 GMT

Hi expert ,

I've been struggling the last 2 days on what a thought would be simple but it appears not .

I just want to sum the positive values .

1st column = employee name
2nd column = week number
3rd col. sum(numb hours worked)
4 th col. sum(weekly hours contract )

as you can see , on week 5 the employee work less than it's weekly hours contract . so in the 4th column total , i just want get rid of negative values and have 4.25 in total in column 5

I tried :

if (column2> column1 , column 2 - column 1 )
maxrange (expression, 0 )
I used aggr in all possible way
Try to combine if + maxrange + aggr and, so far, the solution haven't been found.

Thank you for your help and your expertise !

Have a nice one

Re: positives values

F_B — Tue, 18 Feb 2025 13:26:20 GMT

Hi @sunnybi ,

try with:

Sum(If([Worked Hours] - [Contract Hours] > 0, [Worked Hours] - [Contract Hours]))

Hope this helps

Re: positives values

sunnybi — Tue, 18 Feb 2025 14:07:57 GMT

Hi ,

thanks for your reply, but unfortunately ,the total is missing...

Re: positives values

F_B — Tue, 18 Feb 2025 15:16:10 GMT

Let's see if I understood correctly your target.

Use the whole expression with SUM and you'll get it

Re: positives values

sunnybi — Tue, 18 Feb 2025 15:31:01 GMT

Hi F_B,

you're getting close, but there's a misunderstanding. My bad ! (english is not natural to me)

it's opposite way, what i want it's remaining hours that the employee haven"t done per week . (check week 5 )

I tried your idea in the last column but as you can see the results is not right.

In your picture , did you use sum expression for worked hours ,and contract hours ? how come there's no total ?

In my case , I have to use use sum because the datas are on daily basis and shown into weeks.

Anyway, Thank you for you precious help

Re: positives values

Or — Tue, 18 Feb 2025 15:35:01 GMT

Without seeing the underlying data, it's a bit hard to tell... that said, perhaps something like:

Sum(RangeMax(0, Aggr(Sum(YourFormulaHere),YourDimensionsHere))

Re: positives values

sunnybi — Tue, 18 Feb 2025 15:36:09 GMT

it's understadable, wish we could do visio

Re: positives values

sunnybi — Tue, 18 Feb 2025 15:45:13 GMT

Finally , I had something !

In a straight table , I change total function from Auto , to Sum . And it worked !!

Thing is, this feature don"t exist in a cross table . any advice?

Re: positives values

sunnybi — Tue, 18 Feb 2025 15:51:30 GMT

Hey Or,

thank you for your advice , but ...

feels like this is getting close , but something missing....