Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.
Here is one link where troyansky explains Aggr() and Synthetic dimensions:
Q-Tip # 14 – AGGR() and Synthetic Dimensions | Natural Synergies