topic Show only latest date in QlikView

Show only latest date

fgirardin — Wed, 25 Nov 2020 16:16:04 GMT

Hello fellow Qlik users,

Couldn't find an answer to my issue (maybe I did not look hard enough) , so I will ask you directly:

I use QlikView to make some check to our inventory.

Many articles have multiple "Set Prices" that have been added over the year. It's nice to have all these informations but I'd like to be able to show the latest price using the date field...

Here is an example of my datas

Article Code Article Name Set Price Date

12 AAAA 150 10/10/2012

12 AAAA 155 11/11/2013

13 BBBB 25 15/04/2015

13 BBBB 45 12/02/2016

Like I said, I'd like to show only 1 line for each article (the latest price entry)

So the result would look like something like this:

Article Code Article Name Set Price Date

12 AAAA 155 11/11/2013

13 BBBB 45 12/02/2016

I tried several options, like "firstsortedvalue", etc... but with little results

Any help you can provide will be greatly appreciated !

Also, is it possible to switch between "show all dates" and "show latest date", so I have the option to show all datas / only latest datas on my report ?

Thanks for your help

Re: Show only latest date

sunny_talwar — Tue, 19 Apr 2016 08:34:15 GMT

Try this in straight table:

Dimension:

Article Code

Article Name

Expression:

1) FirstSortedValue([Set Price], -Date)

2) Date(Max(Date))

Re: Show only latest date

Chanty4u — Tue, 19 Apr 2016 08:35:57 GMT

use

Firststoredvalue([Set Price],-Date)

and for max date : Date(Max(Datefield)

Re: Show only latest date

Kushal_Chawda — Tue, 19 Apr 2016 08:36:32 GMT

create the straight table

Dimension:

Artical Code

Artical name

Expression:

1) Set Price : FirstSortedValue(distinct Price, -Date)

2) Date : FirstSortedValue(distinct Date, -Date)

Re: Show only latest date

sunny_talwar — Tue, 19 Apr 2016 08:55:31 GMT

Attached a sample to see how the toggle can work between all rows and last date

Re: Show only latest date

fgirardin — Tue, 19 Apr 2016 12:41:37 GMT

Hello,

Thanks for your help.
I was able to use your example but I still have one issue:

The price does not show

Here are some pics, hope it will help to see whats wrong

1: As you can see, my articles are listed multiple times if they have multiple prices defined during their lifespan (even if the price does not change)

Note that the column "SET PRICE (TEST)" is a control to show what values should be in "SET PRICE"

2: Using your solution (with toggle button), it shows only the latest date, wich is great, but the price never show in the column "SET PRICE"

My Dimensions are

KEY

KEYFIELD

ARTICLE

ARTICLEFIELD

DATE

=Date(DATEFIELD) (the date format in my case is "DD.MM.YYYY HH:MM:SS" so I get rid of the timestamp)

with condition "=vToggle = 0"

Expressions are

DATE

Date(Max(DATEFIELD))

with condition "=vToggle = 1"

SET PRICE

=if(vToggle = 1, FirstSortedValue([PRICEFIELD], -Date), [PRICEFIELD]

I tried without the toggle, I've got the same problem.

Note: The data I collect comes from our ERP

Any Ideas ?

Thanks again

Re: Show only latest date

sunny_talwar — Tue, 19 Apr 2016 14:59:03 GMT

1) Use Date(Floor(DATEFIELD)) here

2) Same thing here -> Date(Floor(Max(DATEFIELD)))

3) Try this: =If(vToggle = 1, FirstSortedValue(DISTINCT [PRICEFIELD], -DATEFIELD), [PRICEFIELD])

Re: Show only latest date

fgirardin — Tue, 19 Apr 2016 15:04:44 GMT

Thank you to all who answered this topic, I appreciate it

The solution provided by Sunny T is working. I made a mistake when using the price and date expression (I was using "DATE" and not the DATEFIELD name)
Sorry for the hiccup

Thanks again !!

Re: Show only latest date

joancasellasvega — Fri, 29 May 2020 08:41:44 GMT

Hi @sunny_talwar ,

I have similiar problem but in Qlik Sense.

I would lke to have a filter (0,1) that identifies or filter (in a table), those INSTANCIA with the latest LASTMODIFIED date.

you solution..

Date(Max(LastModified))

FirstSortedValue(INSTANCIA, -LastModified)

...partially work, when I'm adding more columns at certain point the table show duplicates

I have attached and example with 3 tables that I'm loading with a concatenate and an example of the expected result.

I hope it helps.

Joan

Re: Show only latest date

EmpireDist — Fri, 26 Feb 2021 18:30:05 GMT

How would this work to get the last 3 dates for each Article Code and Article Name?