positives values

[sunnybi]

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 .

1. 1st column = employee name
2. 2nd column = week number
3. 3rd col. sum(numb hours worked)
4. 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  :

1. if (column2> column1 , column 2 - column 1 )
2. maxrange (expression, 0 )
3. I used aggr in all possible way
4. 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

[F_B]

Hi @sunnybi ,

try with:

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

Hope this helps

[sunnybi]

Hi ,

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

[F_B]

Let's see if I understood correctly your target.

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

[sunnybi]

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

[Or · Accepted solution]

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

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

[sunnybi]

it's understadable, wish we could do visio

[sunnybi]

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?

[sunnybi]

Hey Or,

thank you for your advice , but ...

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