Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Maybe try as your expression:
IF([Date of Update]=Max(TOTAL <Project> [Date of Update]),[Date of Update],0)
Hope this helps,
Jason
Try with FirstSortedValue which will help.
May be like FirstSortedValue([Issue Update Command],Aggr([Date of Update],[Issue ID]))
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 ;