Record based on max date

[Mahamed_Qlik]

Hi

I want to consider only records with max (Date), department wise.

ID	DEPT	POSITION_DT	Count( Distinct ID)
100	Computer	1/21/2009	1
100	Finance	5/26/1992
101	Admin	6/26/2016	1
101	Education	8/1/2013
101	Computer	1/1/2007

Also, In chart I want to show the respective department only.

Kindly assist.

Regards,

Mahamed

[Or]

Dimension: ID

Measures: FirstSortedValue(DEPT, -POSITION_DT) and Date(Max(POSITION_DT))

You could also do this by suppressing zero/null values and picking only the latest line in a measure if for some reason you don't want to use measures.

[Mahamed_Qlik]

HI

But I want DEPT to be use as dimension because I want to show latest department wise count

[Or]

The two halves of that statement don't seem to be related? It will show up as a measure. Do you have a specific reason you need it to be a dimension?

[Mahamed_Qlik]

In Detail :

I have two dates ( CalDate and PosDate)

if I select my CalDate as 09/19/2018 then below records are populating which is correct:

ID          DEPT           DEPT_TYPE     PosDate       Count

100      Account      AB                      06/01/2021   1

100      Finance       AB                      03/29/2008   0

100      Finance      AB                       12/25/2011   0

100      Finance      AB                        01/01/2018  0

Now here you can see ID-100 was working in multiple DEPT with different PosDate and as per current logic 
its calculating count as 1 for max of total PosDate.

Expetation is when user select Caldate as 09/19/2018  then  during this period ID was working in Finanance DEPT and its max posDate is 01/01/2018 so this record needs to shown in count as 1.

expected result:

ID          DEPT           DEPT_TYPE     PosDate       Count

100      Account      AB                      06/01/2021   -                       <---- this record can be null or removed as Caldate is                                                                                                                                       backdated date selected

100      Finance       AB                      03/29/2008   0

100      Finance      AB                       12/25/2011   0

100      Finance      AB                        01/01/2018  1                <--- this needs to shown only for backdated calDate with                                                                                                                           max posDate

How to achieve this in set analysis

[Mahamed_Qlik]

No Solution???

[Mahamed_Qlik]

Awaiting for solution