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

[mohdhaniff]

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:

[khalander]

Hi,

Please try below expression

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

[mohdhaniff]

Hi dada khalander,

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

[rahulpawarb]

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

[mohdhaniff]

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.

[jonathandienst · Accepted solution]

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)

or

SET DateFormat='d/M/YYYY';

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

[mohdhaniff]

Thanks Jonathan for your solution.