Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table with theses field and an example:
SNumber Date Val Step 1 Date Val Step 2 Service Code
11111 10/05/12 10/15/12 1
11111 10/05/12 10/15/12 2
11111 10/05/12 10/12/12 4
11111 10/05/12 10/18/12 6
I would like to make the Duration of the difference between theses dates and take the Max of this Duration
Like:
SNumber Date Val Step 1 Date Val Step 2 Service Code Duration
11111 10/05/12 10/15/12 1 10
11111 10/05/12 10/15/12 2 10
11111 10/05/12 10/12/12 4 7
11111 10/05/12 10/18/12 6 13
So take the max of this value and and at the final just:
11111 10/05/12 10/18/12 6 13
For that in my table, I put the expression:
avg(aggr(max(Duration),SNumber))
But my code doesn't work, I have the good duration and the good Max, but the Service Code for the Max Duration is not good ....
I have just:
11111 10/05/12 10/18/12 1 13
This is my code:
D_WORKFLOW_TMP: D_WORKFLOW: |
Can you help me with my problem please?
Maybe the max duration occurs in several records. In that case the firstsortedvalue cannot determine which one should be used so it returns null.
try aggr(firstsortedvalue([Service Code],-Duration),SNumber)
Thanks for the answer.
It's not working, I tried aggr(firstsortedvalue([Service Code],-Duration),SNumber) and aggr(max(firstsortedvalue([Service Code],-Duration)),SNumber) but no one works in the expressions.
Do you have an another idea please?
Oh okey; now with all field with this expression, it's better, itùs working on some of it. But some other are still empty ..... I don' know why.
For exemple if I have:
11111 02/27/2009 02/20/2009 2 7
11111 02/27/2009 02/20/2009 3 7
11111 03/10/2009 02/20/2009 8 18
I Should have:
11111 03/10/2009 02/20/2009 8 18
But I have an empty values on all the fields in expression. Except the SNumber in dimention ....
But for exemple:
11112 03/11/2009 03/10/2009 2 1
11112 03/11/2009 03/10/2009 8 1
11112 03/12/2009 03/10/2009 3 2
This one is working great:
11112 03/12/2009 03/10/2009 3 2
So .... I don't understand why one works and not an another one ... And itùs like half working and half not ...
:S
Maybe the max duration occurs in several records. In that case the firstsortedvalue cannot determine which one should be used so it returns null.
Yeah, I put a DISTINCT aggr(firstsortedvalue(DISTINCT [Service Code],-Duration),SNumber), and it seems to work.
Thank you very much