Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
JustTryingToLearn
Contributor III
Contributor III

Showing only data with the last append date

I am looking for a method to only show data that comes from the last append date from my data source. My data looks like this:

[Current Date] | [Employee ID] | [Office]  | [Job Title]

01/01/2021       | John Adams     | Tokyo     | Sales

01/01/2021       | Mark Smith       | Chicago | Analytics

01/01/2021       | Dan Williams    | London  | Illustrator

01/01/2020       | John Adams     | Tokyo     | Sales

01/01/2020       | Mark Smith       | Chicago | Analytics

01/01/2020       | Dan Williams    | London  | Illustrator

01/01/2020       | Mary Carter       | Oxford    | Receptionist

01/01/2019       | Mark Smith       | Chicago | Analytics

01/01/2019       | Dan Williams    | London  | Illustrator

01/01/2019       | Mary Carter       | Oxford    | Receptionist

 

I want the final data to look like this in the app:

01/01/2021       | John Adams     | Tokyo     | Sales

01/01/2021       | Mark Smith       | Chicago | Analytics

01/01/2021       | Dan Williams    | London  | Illustrator

 

As you can see, I pulled all the data from the most recent data the was appended to my dataset. I need this to be an expression that I add to formulas for charts, tables, and KPIs - it cannot be a restriction in the data load editor (because the full dataset is used is a section of the app).

Does anyone have a formula or something that can help me do this?

Thank you!

1 Solution

Accepted Solutions
Ponkaviyarasu
Partner
Partner

Hi,

For above example,

you can modify the measure from count([Employee ID]) to Count({<Date={'$(vMaxDate)'}>}Employee_ID).

 

Thanks.

View solution in original post

3 Replies
Ponkaviyarasu
Partner
Partner

Hi,

Hope this will help!

#1: Create a variable: 'vMaxDate'  assign like  = Max([Current Date])

#2: For ex: in table Chart, use this expression: =aggr(MAx({<Date={'$(vMaxDate)'}>}Date),Date)

and uncheck 'include null values'. It will restrict other rows.

i believe this is your expectation.

Thanks

 

JustTryingToLearn
Contributor III
Contributor III
Author

This makes sense so far - but what If I am using this to help shape a chart that does not use the date function in it?

 

I have a chart that has the [Office] as a stack, and [Job Title] as part of the bars on the x-axis, with the count([Employee ID]) as the y-axis. How can I use vMaxDate to filter this down?

Ponkaviyarasu
Partner
Partner

Hi,

For above example,

you can modify the measure from count([Employee ID]) to Count({<Date={'$(vMaxDate)'}>}Employee_ID).

 

Thanks.

View solution in original post