Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead 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)


Please see the document attached.


Let me know if you have any solutions.


Thanks



1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

3 Replies
sunny_talwar

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.

sunny_talwar

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

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