topic Re: Aggr Question in QlikView

Aggr Question

Mon, 17 Jun 2013 13:54:47 GMT

Hi all,

I am still trying to wrap my head around the Aggr() function, so I need some help! I want to group a set of data by ID, and then know how many distinct string values are attributed to that ID. Once I know that there is more than one unique string, I'd like to display the ID as a dimension. That said, I have this as my dimension expression:

=if(aggr(Count(DISTINCT UniqueString), ID) > 1, ID, Null())

This is returning all Null values. Please help me resove the issue.

Thanks,

Paul

Re: Aggr Question

prodanov — Mon, 17 Jun 2013 14:11:59 GMT

Hi,

Whar is your expression? It may give a zero or null.

Regards

Re: Aggr Question

Mon, 17 Jun 2013 14:23:24 GMT

My expression is in the dimension field (custom dimension expression).

Re: Aggr Question

prodanov — Mon, 17 Jun 2013 14:26:21 GMT

You must have an expression! enter a expression sum(1) and return

Re: Aggr Question

calvindk — Mon, 17 Jun 2013 14:37:12 GMT

Try adding both dimension

ID,

and calculated dimension

=if(Count(DISTINCT UniqueString) > 1, ID, Null())

and then do Suppress if NULL on calculated dimension

go to presentation tab

and hide ID

Re: Aggr Question

Mon, 17 Jun 2013 14:52:21 GMT

Ahh sorry, I misunderstood your question. I DO have an expression - it's to choose the FirstSortedValue() of unique string based on a date. I double checked my expression and I had reversed the order of the parameters in FirstSortedValue() - I had date first and then UniqueString. *FACEPALM*

I corrected the expression and VOILA. This was a simple/stupid error - thanks for the suggestion and for keeping me sane!

-Paul