topic Re: Qlik View Expression for Max Year (-1) and Max Date in QlikView

Qlik View Expression for Max Year (-1) and Max Date

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

Hi,

Currently i got multiple records in database. How can I create expression to retrieve data with specific Year and Max Date.

Sample Data:

LOAD * INLINE [

Record, Date, Year

abc, 7/9/2015, 2015

abc, 6/8/2015, 2015

abc, 4/1/2015, 2015

abc, 12/3/2016, 2016

abc, 16/2/2016, 2016

abc, 6/1/2016, 2016

abc, 1/1/2017, 2017

abc, 6/2/2017, 2017

];

I only able to get the required Year but not the max date:

My Expression:

=count({$<Year={'$(=max(Year)-1)'}>} Record)

My Result:

My Expectation Result:

Re: Qlik View Expression for Max Year (-1) and Max Date

khalander — Mon, 13 Feb 2017 04:41:35 GMT

Hi,

Please try below expression

=count({$<Year={'$(=max(Year)-1)'},Date={'$(=max(Date))'}>} Record)

Re: Qlik View Expression for Max Year (-1) and Max Date

mohdhaniff — Mon, 13 Feb 2017 05:01:47 GMT

Hi dada khalander,

Thanks but the formula not work. I got zero (0) result

Re: Qlik View Expression for Max Year (-1) and Max Date

rahulpawarb — Mon, 13 Feb 2017 05:19:35 GMT

Hello Haniff,

As a workaround I have created script level solution for this problem. Created a Resident Load to pull maximum date for each Record and Year (refer below given sample script).

Data:

LOAD * INLINE [

Record, Date, Year

abc, 7/9/2015, 2015

abc, 6/8/2015, 2015

abc, 4/1/2015, 2015

abc, 12/3/2016, 2016

abc, 16/2/2016, 2016

abc, 6/1/2016, 2016

abc, 1/1/2017, 2017

abc, 6/2/2017, 2017

];

NoConcatenate

NewData:

LOAD

Record AS NewRecord,

Year AS NewYear,

Date(Max(Date),'MM/DD/YYYY') AS NewDate

Resident Data

Group By Record, Year;

Regards!

Rahul

Re: Qlik View Expression for Max Year (-1) and Max Date

mohdhaniff — Mon, 13 Feb 2017 06:29:35 GMT

Hi Rahul,

Sorry. I use QV Personal Edition and cannot open your attachment.

By using the script provided by you, now I got 2 tables and the data become bigger. Can you help me to copy the expression script from your sample?

Regards.

Re: Qlik View Expression for Max Year (-1) and Max Date

jonathandienst — Mon, 13 Feb 2017 06:32:10 GMT

Your problem is around date formats. The dates are loading as 'd/M/yyyy', but your environment setting is probably 'dd/MM/yyyy'. Try:

SET DateFormat='dd/MM/YYYY';

=Count({<Date = {"$(=Date(Max({<Year={'$(=Max(Year)-1)'}>} Date), 'd/M/yyyy'))"}>} Record)

SET DateFormat='d/M/YYYY';

=Count({<Date = {"$(=Date(Max({<Year={'$(=Max(Year)-1)'}>} Date)))"}>} Record)

Re: Qlik View Expression for Max Year (-1) and Max Date

mohdhaniff — Mon, 13 Feb 2017 06:44:17 GMT

Thanks Jonathan for your solution.