Show only latest date

[fgirardin]

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

[sunny_talwar]

Try this in straight table:

Dimension:

Article Code

Article Name

Expression:

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

2) Date(Max(Date))

[Chanty4u]

use

Firststoredvalue([Set Price],-Date)

and for max date : Date(Max(Datefield)

[Kushal_Chawda]

create the straight table

Dimension:

Artical Code

Artical name

Expression:

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

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

[sunny_talwar · Accepted solution]

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

[fgirardin]

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

[sunny_talwar]

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])

[fgirardin]

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 !!

[joancasellasvega]

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

[EmpireDist]

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