Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi ,
try this
Count(Aggr(Max(ID), DEPT))
refer : https://www.analyticsvidhya.com/blog/2014/02/aggr/
regards
Vikas
Thanks vikas,
But I want to display max(position) date record
Count( Distinct If(Aggr(NODISTINCT Max( TOTAL <DEPT> POSITION_DT),DEPT,id) = POSITION_DT, id))
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.
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)
Thanks Vineeth
But I want to show in bar chart and dimension should be DESC not id then how?
Dimension
=if(aggr(nodistinct max(HIST_ID),ID)=HIST_ID,DESC) << uncheck show null values
Measure
=count(ID)
@Mahamed_Qlik kindly close the thread by marking a response as solution