Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
fgirardin
Creator
Creator

Show only latest date

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

9 Replies
sunny_talwar

Try this in straight table:

Dimension:

Article Code

Article Name

Expression:

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

2) Date(Max(Date))

Chanty4u
MVP
MVP

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

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

fgirardin
Creator
Creator
Author

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"

qv_date001.PNG

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"

qv_date002.PNG

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

Capture.PNG

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

Capture.PNG

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

fgirardin
Creator
Creator
Author

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
Partner - Contributor III
Partner - Contributor III

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

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