Announcements
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Discrepancy in Result with Pick Function in Chart

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(DimCount(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)

Let me know if you have any solutions.

Thanks

1 Solution

Accepted Solutions
MVP

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(DimCount(Distinct if(aggr(NODISTINCT sum([Inv Variance]), [Tran No]) <> 0, [Tran No])),2)

and see if it changes anyting

3 Replies
MVP

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(DimCount(Distinct if(aggr(NODISTINCT sum([Inv Variance]), [Tran No]) <> 0, [Tran No])),2)

and see if it changes anyting

Anonymous
Not applicable
Author

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.

MVP

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

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

Community Browser