AGGR function

[Mahamed_Qlik]

Hi Experts,

I have below table, wherein I want to display only single row with max (position date):

POSITION_DT	CREATION_DT	DEPT	id
04/27/2005	10/21/2020	A	1
04/27/2005	10/21/2020	B	1
12/25/2011	10/21/2020	A	1
09/10/2020	10/21/2020	B	1

Please help me with the set analysis expression to be written in bar chart where my dimension will be DEPT and measure to calculate count (distinct id)

Regards,

Mahamed

[vikasmahajan]

Hi ,

try this

Count(Aggr(Max(ID), DEPT))

refer : https://www.analyticsvidhya.com/blog/2014/02/aggr/

regards

Vikas

[Mahamed_Qlik]

Thanks vikas,

But I want to display max(position) date record

[Iswarya_]

Hi @Mahamed_Qlik 

Count( Distinct If(Aggr(NODISTINCT Max( TOTAL <DEPT> POSITION_DT),DEPT,id) = POSITION_DT, id))

[Mahamed_Qlik]

THanks Ishwarya.

I have one more scenario as below  :

ID	DESC	HIST_ID	Count(ID)
112	Description1	200	1
112	Description1	100	0
112	Description2	150	0
114	Description3	300	0
114	Description3	400	1

In above table, we want to get the count of distinct ID only for its maximum HIST_ID

expected result :

ID	DESC	HIST_ID	Count(ID)
112	Description1	200	1
114	Description3	400	1

Count (distinct ID) should be 2.

[vinieme12]

Use Calculated dimensions

Dimensions 

ID 

Description 

=Aggr(If(Hist_Id=Max(Hist_Id),Hist_Id),ID) 

Uncheck show null values for this calculated dimension 

Measure = count(ID)

[Mahamed_Qlik]

Thanks Vineeth 

But I want to show in bar chart and dimension should be DESC not id then how?

[vinieme12]

Dimension

=if(aggr(nodistinct max(HIST_ID),ID)=HIST_ID,DESC)   <<  uncheck show null values

Measure

=count(ID)

[vinieme12]

@Mahamed_Qlik kindly close the thread by marking a response as solution