Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I've been having an issue manipulating the total value. In the example I've attached the total result is 240, but I don't want that value.
I put a condition on the #Value1/hour column: If Error <> ' ' or Time(min) <> 0, (#Value1*60) / Time(min). The issue is I only want to calculate the lines where the condition verifies. Since the line 2 has Time=0, I don't want its Value1 because it will distort my results. What I expect to see in column Value1/hour as a total is (70*60)/30=140 and not (100*60)/30=200, i.e, only the lines in which the condition would verify. I also toyed with Total Mode, but it doesn't work for what I want. How can I do this? Any doubts feel free to ask, I'll try to explain better. Thank you very much!
EDIT: edited to have a better example
In your expression change some thing this ways
=IF(Error<>' ' or [Time(min)]<>0, Sum(aggr( sum(#Value1)*60/sum([Time(min)]),DocumentNr)) )
Use AGGR here
Check attached file also.
Regards
Anand
Did you try sum of rows in the total mode, it works normally?
Hello Anand,
Thank you for trying to help me!
I tried what you said but it didn't really work, in your example the value is the correct one but is a consequence of having 2 lines only. I edited and added a third one in my example to make it more complete. As soon as I tried what you said with a third line it promptly summed to 270.
I did, but I am diving values and that is not how I am supposed to get the total.