topic Re: Max function in QlikView

Max function

Thu, 06 Sep 2012 15:39:10 GMT

Hi All,

I have a set of projects which have issues logged against them. Each of the Issues can have multiple Issue Update Comments.

I would like to be able to evaluate the 'Date of Update' column and display the newest 'Issue Update Comment', highlighted below in blue.

I understand that this can be done using the 'max' function on the 'Date of Update' field. Unfortunately I am having some difficulty implementing this.

I have been able to successfully retrieve the latest update date using the below function in the script (and grouping by Issue ID):

max(DateOfUpdate) as IssueUpdateLatest

However I am unable to return only the latest update which results in duplicate records in any tables created.

My data looks like this:

Project	Issue	Issue ID	Issue Update ID	Issue Update Comment	Date of Update
Alpha	Funding Issue	100	101	Recently identified an issue with the funding	01/02/12
Alpha	Funding Issue	100	102	Have spoken to John and am working on fix	15/02/12
Bravo	Resource Issue	110	111	Resource probs	01/03/12
Bravo	Resource Issue	120	121	Resource probs update	12/08/12
Charlie	Branding decision	130	131	Logo decision not made	28/08/12

Any help would be much appreciated.

Many thanks,

Alan

Re: Max function

Jason_Michaelides — Thu, 06 Sep 2012 16:05:26 GMT

Beware of performing the Max() in the script as it will ignore any user-selections.

In a straight-table chart, with all of the fields in your table above as dimensions, add this expression:

Max(TOTAL <Project> [Date of Update])

This will show you the date of the latest update for each project (just to demonstrate the logic). Now, in the dimension tab, expand the + next to Issue Update Comment and double-click "Text Color". In the expression box, write:

IF([Date of Update]=Max(TOTAL <Project> [Date of Update]),Blue(),Black())

Hope this helps,

Jason

Re: Max function

Fri, 07 Sep 2012 12:19:12 GMT

Hi Jason,

Thanks for your very helpful response.

If I wanted to limit the records displayed so that only the latest record was shown, how could that be done?

Thanks

Alan

Re: Max function

Jason_Michaelides — Sun, 09 Sep 2012 08:24:41 GMT

Maybe try as your expression:

IF([Date of Update]=Max(TOTAL <Project> [Date of Update]),[Date of Update],0)

Hope this helps,

Jason

Re: Max function

CELAMBARASAN — Sun, 09 Sep 2012 10:38:28 GMT

Try with FirstSortedValue which will help.

May be like FirstSortedValue([Issue Update Command],Aggr([Date of Update],[Issue ID]))

Re: Max function

Fri, 07 Aug 2015 09:25:19 GMT

table:
LOAD Project,
Issue,
[Issue ID],
[Issue Update ID],
[Issue Update Comment],
[Date of Update],
[Date of Update]&'_'&Project as _key
FROM
D:\QlikView\AXS\data\test.xlsx
(ooxml, embedded labels, table is Feuil1);

Mytable:
Load Project as ID,
max(date([Date of Update],'DD/MM/YYYY')) as maxdate
Resident table
group by Project;

Let ID= fieldvalue('maxdate',fieldindex('ID','')) ;

projet:
NoConcatenate
LOAD
maxdate&'_'&ID as _key,
maxdate
Resident Mytable;

Inner join
LOAD *
Resident table;

drop table table, Mytable ;