topic AGGR Question in QlikView

AGGR Question

posywang — Thu, 02 Feb 2017 18:20:00 GMT

Please check the attached sample file. My goal is to hide resources that have allocation as 1 FTE but somehow Becky in example can't be hidden. Please help. Thanks!

Re: AGGR Question

mayankraoka — Thu, 02 Feb 2017 18:34:52 GMT

Hi Xia,

Please try below expression:

if(aggr( sum( {<Version={'Planning Tool'}> } AssignedFTE),Mth)=1,

aggr(sum( {<Version={'Planning Tool'} > } AssignedFTE),Mth))

Regards,

Mayank

Re: AGGR Question

posywang — Thu, 02 Feb 2017 18:38:45 GMT

It's not working...

It now shows 2.8 under Becky's name...

Re: AGGR Question

sunny_talwar — Thu, 02 Feb 2017 19:35:51 GMT

Seems like the rounding issue

If(Round(Sum({<Version={'Planning Tool'}> } AssignedFTE), 0.0000000000001) = 1, 0, Sum({<Version={'Planning Tool'}> } AssignedFTE))

Re: AGGR Question

posywang — Thu, 02 Feb 2017 19:43:50 GMT

Works like a charm! Can you please let me know why we need to round by 0.0000000000001? The source data looks fine to me. Thanks again!

Re: AGGR Question

sunny_talwar — Thu, 02 Feb 2017 19:47:28 GMT

The numbers you are summing are 0.3 and 0.35... it seems that although it gets very close to 1, but never actually get 1. So, by rounding to a really small number, we are forcing it to be 1. I think Round(Exp, 0.0001) should work too... but I did this just in case