latest record

[Mahamed_Qlik]

Hi all,

I have below table records :

ID	DEPT	TYPE	POS_DATE
115	Finance	PRESENT	06/01/2021
115	Admin	PRESENT	03/29/2008
115	Admin	PRESENT	12/25/2011
115	IT	PRESENT	10/08/2012
115	IT	PRESENT	08/01/2013
115	IT	PRESENT	01/01/2018

Expectation -

There is another date field available in filters from when user select date then according to selected date the max POS_Date records to be displayed.

For example - If any user select the date from filter as "01/02/2018" then according to this max (POS_date) to be display as 01/01/2018...

so In bar chart dimension to be shown as IT and its respective measure count (ID) should be as 1

[SerhanKaraer]

Hello Mahamedfaijan,

You should set measure expression as count({<POS_DATE={"<=$(=Only(Date))"}>} ID) assuming user selects from Date field and selects only one value each time.

[Mahamed_Qlik]

HI Serhan

I have tried your logic but it is returning all the if select date as 01/02/2018

115	Admin	PRESENT	03/29/2008
115	Admin	PRESENT	12/25/2011
115	IT	PRESENT	10/08/2012
115	IT	PRESENT	08/01/2013
115	IT	PRESENT	01/01/2018

Expectation is to return only max POS_Date for the selected Date...it should return only 01/01/2018

[BrunPierre]

@SerhanKaraer's expression returns all POS_Dates in the table prior to a user-selected date.

This, however, will return only IDs for the max POS_Date

Count({<POS_DATE={"$(=Max(Date))"}>} ID)

[Mahamed_Qlik]

Hi Brun

As per your logic It is returning record for 06/01/2021 which is max of all the POS Date but I want 01/01/2018 because we have year selected as 2018

[BrunPierre]

Until a year value is selected the expression by default returns the count for the max POS_Date i.e.06/01/2021.

It response dynamically upon year selection as shown below.

[Mahamed_Qlik]

Hi Brun

In my case the year field is not derived from POS_Date, is it derived from another date field and we are just comparing it with POS_Date

[Mahamed_Qlik]

Hi Brun

Got any solution??

[MayilVahanan]

Hi

Try like below

Dim: Dept

Measure: =Count({<POS_DATE={"$(=Date(Max(Aggr(if(POS_DATE <= Max(Date), POS_DATE), POS_DATE))))"}>}ID)

[Mahamed_Qlik]

Hi Mayi

Given set analysis is working fine on selection of date from Date field but it is not working on yearly basis.
for single date selection it is working fine.