topic Re: How to display latest year? in QlikView

How to display latest year?

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

Hi All,

I have a small issue.

I have two filters one is from-date and to-date ,when I select this two dates for particular field say project I should get the latest data related to project.

ex:

project-name ,data

project A ,nov-2017

project A,jan-2018

project B,dec-2017

project B,jan 2018

project C,jan 2017

this is my data if i select filter nov-2017 to dec-2018 then for project A The latest data was updated on jan-2018.how can I achieve when i select filter I shoud get the latest month of the project.?

please help me:)

Thanks in advance.

Re: How to display latest year?

arvind_patil — Fri, 23 Feb 2018 13:01:18 GMT

Hi Prashantji,

may be like below:

Date(max(Date#(data,'MMM-YYYY')),'MMM-YYYY')

Thanks,

Arvind Patil

Re: How to display latest year?

buzzy996 — Fri, 23 Feb 2018 16:49:46 GMT

convert ur data of date format something like 01/urmonth/year and pull max date from this format.

Re: How to display latest year?

zebhashmi — Fri, 23 Feb 2018 20:13:29 GMT

Max(aggr(date,project-name)

Re: How to display latest year?

zebhashmi — Fri, 23 Feb 2018 20:57:45 GMT

aggr(max(date),project-name)

Re: How to display latest year?

Chanty4u — Sat, 24 Feb 2018 16:43:41 GMT

try this

LOAD * INLINE [

project-name, data

project A, nov-2017

project A, jan-2018

project B, dec-2017

project B, jan 2018

project C, jan 2017

];

LOAD

*,Date(max(Date#(data,'MMM-YYYY')),'MMM-YYYY') as new

Resident A

Group by "project-name",data;

Re: How to display latest year?

vongalaprashant — Mon, 26 Feb 2018 09:57:58 GMT

Hi All,

Thanks for your replies.I need a small favour here :

As you uys said I seperated month and year In above example,if I select nov-2015 to dec-2018 as filter .

I am getting latest year for project A that is 2018.but I need latest month also the latest month is Jan.

If I use max(month(data)) it retuns dec , but I need jan.

Thanks in advance.

Re: How to display latest year?

tresB — Mon, 26 Feb 2018 10:57:39 GMT

Try month() as the outermost function like: = Month(Max(data))