Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
OmarBenSalem

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?

1 Solution

Accepted Solutions
sunny_talwar

Try this

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

View solution in original post

11 Replies
sunny_talwar

May be this

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

OmarBenSalem
Author

Nope !

Here's an example: C should be 2 :

Capture.PNG

sunny_talwar

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

OmarBenSalem
Author

It's always returning 1 for each ID:

Capture.PNG

sunny_talwar

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

OmarBenSalem
Author

Just these 2 fields sunny

sunny_talwar

Try this

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

OmarBenSalem
Author

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)

sunny_talwar

You will probably have to create a new field like this

LOAD AutoNumber(ID&Date) as Key

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