Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Max function

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:

ProjectIssueIssue IDIssue Update IDIssue Update CommentDate of Update
AlphaFunding Issue100101Recently identified an issue with  the funding01/02/12
AlphaFunding Issue100102Have spoken to John and am working on fix15/02/12
BravoResource Issue110111Resource probs01/03/12
BravoResource Issue120121Resource probs update12/08/12
CharlieBranding decision130131Logo decision not made28/08/12

Any help would be much appreciated.

Many thanks,

Alan

Tags (3)
5 Replies
jason_michaelid
Honored Contributor II

Re: Max function

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

Not applicable

Re: Max function

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

jason_michaelid
Honored Contributor II

Re: Max function

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

Try with FirstSortedValue which will help.

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

Not applicable

Re: Max function

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 ;