topic Re: Conditional Total Result in QlikView

Conditional Total Result

afbraga1 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Conditional Total Result

its_anandrjs — Sun, 28 May 2017 16:33:25 GMT

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

Re: Conditional Total Result

Digvijay_Singh — Sun, 28 May 2017 16:35:32 GMT

Did you try sum of rows in the total mode, it works normally?

Re: Conditional Total Result

afbraga1 — Sun, 28 May 2017 17:10:37 GMT

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.

Re: Conditional Total Result

afbraga1 — Sun, 28 May 2017 17:11:30 GMT

I did, but I am diving values and that is not how I am supposed to get the total.