Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Firstsortedvalue

Hello,

I am trying to pick most recent date from Order table by Date and Product, but I have run into a problem when an item appears on multiple lines.

Here's an example when my script fails:

date(FirstSortedValue(InvoiceDate,-if(InvoiceDate >= monthstart(AddMonths(today(),-6)), InvoiceDate)))

Product     InvoiceDate

505ABC     07/08/2016

505ABC     07/08/2016

How do I tell it to pick the most recent or just one from each date.

Thanks

Vibol

4 Replies
sushil353
Honored Contributor II

Re: Firstsortedvalue

Hi,

Try something like this:

date(FirstSortedValue(Distinct InvoiceDate,-Aggr(Max({<InvoiceDate ={"$(=monthstart(AddMonths(today(),-6)))"} InvoiceDate),InvoiceDate,Product))

for better resolution .. attach some sample data or application

HTH

antoniotiman
Honored Contributor III

Re: Firstsortedvalue

Try

Date(FirstSortedValue({<InvoiceDate={'>=$(=MontStart(Today(),-6))}>} DISTINCT InvoiceDate,-InvoiceDate))

MVP
MVP

Re: Firstsortedvalue

If you have same product values multiple times for a date, you could use Distinct keyword like:

FirstSortedValue( Distinct Product, -InvoiceDate)

Note: Use of 'distinct' could give red-lined error message, just ignore that.

Not applicable

Re: Firstsortedvalue

Thank You for your help.  Distinct was all I needed. 

date(FirstSortedValue(Distinct InvoiceDate,-if(InvoiceDate >= monthstart(AddMonths(today(),-6)), InvoiceDate)))

Community Browser