11 Replies Latest reply: Dec 4, 2017 9:51 AM by Sunny Talwar

Use aggr() in a set expression

Hi Qlikers ( stalwar1 )

Suppose I have a column ID and a column Date

example:

ID , Date

1, 2015

1,2016

1,2016

2,2017

3,2017

3,2017

3,2017

I want to know, how many times each ID is present in its max(Date)

I mean:

ID, max(Date) by ID, count ID for its max(Date)

1, 2016 , 2 times

2, 2017 , 1 time

3, 2017, 3 times

To do so:

I created a simple table:

as a dimension:

ID

as measures:

max(Date)

and

count({<Date={"\$(=aggr(max(Date),ID))"}>} ID) : but this does not seem to work; am I missing sthing in the syntax/logic?

• Re: Use aggr() in a set expression

May be this

Sum(Aggr(If(Date = Max(TOTAL <ID> Date), 1, 0), ID, Date))

• Re: Use aggr() in a set expression

Nope !

Here's an example: C should be 2 :

• Re: Use aggr() in a set expression

I guess since we are aggregating over ID and Date, it is causing an issue... how about this

Count(Aggr(If(Date = Max(TOTAL <ID> Date), ID), ID, Date))

• Re: Use aggr() in a set expression

It's always returning 1 for each ID:

• Re: Use aggr() in a set expression

Do you have another field in your app? or just these two?

• Re: Use aggr() in a set expression

Just these 2 fields sunny

• Re: Use aggr() in a set expression

Try this

Sum(Aggr(If(Date = Max(TOTAL <ID> Date), Count(ID)), ID, Date))

• Re: Use aggr() in a set expression

Finally ! Yes this did it !

Can't we integrate the agg within the set expression? I mean sthing like I did?

count({<Date={"\$(=aggr(max(Date),ID))"}>} ID)

• Re: Use aggr() in a set expression

You will probably have to create a new field like this

and then this

Count({<Key = {"=Max(Date) = Max(TOTAL <ID> Aggr(Date), ID, Date))"}>}ID)

Have not tested, but have a feeling that it should work

• Re: Use aggr() in a set expression

haha! I prefer the first method

• Re: Use aggr() in a set expression

But did it work?