Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table with the following main fields (among others); [Task], [Change date], [Delivery deadline], [Progress %]
The table contents could look like this:
1, 2013-12-01, 2013-12-30, 0
1, 2013-12-05, 2013-12-28, 50
2, 2013-12-01, 2013-12-13, 0
2, 2013-12-02, 2013-12-12, 0
2, 2013-12-10, 2013-12-15, 50
2, 2013-12-12, 2013-12-13, 100
2, 2013-12-15, 2013-12-13, 100
My problem is to write an expression to get the correct Delivery deadline for the latest Change date (and that for each Task).
I only have 10 Tasks so I will make a separate expression for each one.
So for Task 1 it would be 2013-12-28 and for Task 2: 2013-12-13
I have already solved what I thought was a harder expression; to find the first change date when Progress is 100, but that expression won't help me with this one...
Data:
Load * inline
[Task, Change date, Delivery deadline, Progress %
1, 2013-12-01, 2013-12-30, 0
1, 2013-12-05, 2013-12-28, 50
2, 2013-12-01, 2013-12-13, 0
2, 2013-12-02, 2013-12-12, 0
2, 2013-12-10, 2013-12-15, 50
2, 2013-12-12, 2013-12-13, 100
2, 2013-12-15, 2013-12-13, 100];
Inner Join
Load
Task,
Max([Change date]) as [Change date]
Resident Data
Group By Task;
/HIC
Data:
Load * inline
[Task, Change date, Delivery deadline, Progress %
1, 2013-12-01, 2013-12-30, 0
1, 2013-12-05, 2013-12-28, 50
2, 2013-12-01, 2013-12-13, 0
2, 2013-12-02, 2013-12-12, 0
2, 2013-12-10, 2013-12-15, 50
2, 2013-12-12, 2013-12-13, 100
2, 2013-12-15, 2013-12-13, 100];
Inner Join
Load
Task,
Max([Change date]) as [Change date]
Resident Data
Group By Task;
/HIC
Try with a link table with only the task number and the max update date
see exemple
Tack Henric,
Would probably work for me (I ran it and it works of course), but I would rather not do it in the load section, but instead have an expression that find the value(s) for me.
// Peter
Then you should make a chart with Task as dimension and
FirstSortedValue([Delivery deadline],-[Change date])
as expression.
HIC
Henric,
I did as you first suggested and solved it as an inner join in the load section.
But now I found out that my clever expression to find the first Change date with the Progress =100 didn't work in all cases. How would you suggest a solution to find this?
In task 1: there would be none found but in task 2: it would return 2013-12-12.
Found the solution... Just made a new load statement with a "Where Progress=100" and that solved it.
So now I have three tables: The original with a lot of columns, a table with the Max change date and a table with the Min change date when the progress is 100%.
So thanks for the help.
// Peter