topic Re: Discrepancy in Result with Pick Function in Chart in QlikView

Discrepancy in Result with Pick Function in Chart

Anonymous — Mon, 05 Dec 2016 17:31:56 GMT

Hi Experts,

I am experiencing a strange issue. I am not sure whether it's because of aggregation function or something else.

I am getting different result when using the same expression with pick function and without pick function. The result without pick is correct. I am not able to figure out why the value changing.

Dim
1
2

Exp1:

Pick(Dim, Count(Distinct if(aggr(sum([Inv Variance]), [Tran No]) <> 0, [Tran No])),2)

This gives result 184 (incorrect)

Exp2:

Count(Distinct if(aggr(sum([Inv Variance]), [Tran No]) <> 0, [Tran No]))

This gives result 152 (correct)

Please see the document attached.

Let me know if you have any solutions.

Thanks

Re: Discrepancy in Result with Pick Function in Chart

sunny_talwar — Mon, 05 Dec 2016 17:45:25 GMT

The problem is related to Aggr() function... Within Island/Synthetic dimension, Aggr() doesn't behave the way we would expect... what do you get with this?

Pick(Dim, Count(Distinct if(aggr(NODISTINCT sum([Inv Variance]), [Tran No]) <> 0, [Tran No])),2)

and see if it changes anyting

Re: Discrepancy in Result with Pick Function in Chart

Anonymous — Mon, 05 Dec 2016 19:27:03 GMT

Thanks Sunny, It's working perfectly. But I am still researching on rationale, if you can shed some light. I want to understand what difference nodistinct is making there.

Re: Discrepancy in Result with Pick Function in Chart

sunny_talwar — Mon, 05 Dec 2016 19:29:32 GMT

Here is one link where troyansky‌ explains Aggr() and Synthetic dimensions:

Q-Tip # 14 – AGGR() and Synthetic Dimensions | Natural Synergies