| Author |
Message |
achandana01
Groupie
Joined: 20 Jul 2011
Online Status: Offline
Posts: 59
|
   Topic: display date in two columns
Posted: 31 Jan 2014 at 10:33am |
Hello All,
I have data something like this
| ID |
Line |
Status |
Date |
| 100 |
1 |
Started |
1/1/2014 |
| 100 |
2 |
Inprogress |
1/2/2014 |
| 100 |
3 |
sent |
1/3/2014 |
| 100 |
4 |
fulfilled |
1/4/2014 |
| 100 |
5 |
Received |
1/5/2014 |
| 100 |
6 |
sent |
1/6/2014 |
| 100 |
7 |
fulfilled |
1/10/2014 |
| 100 |
8 |
closed |
1/11/2014 |
I need to report the date when status went to "inprogress" and the last day when status turned to fulfilled.
In the above scenerio I want the data to be displayed like this
ID Date in Inprogress Last day it fulfilled
100 1/2/2014 1/10/2014
I have no clue where to start inorder to display data like this.Please help.
Thanks,
|
|
chand
|
 IP Logged |
|
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|
Posted: 31 Jan 2014 at 12:15pm |
|
try this
first group on id
create 2 formulas
@inprogress
if STATUS = "inprogress" THEN DATE
@fulfilled
IF STATUS ="fulfilled" THEN DATE
2 SUMMARIES
@fulfilled2
maximum(@fulfilled,id)
@inprogress2
maximum(@inprogress,id)
place the summaries into group heather or footer
Edited by kostya1122 - 31 Jan 2014 at 12:17pm
|
|
IP Logged |
|
achandana01
Groupie
Joined: 20 Jul 2011
Online Status: Offline
Posts: 59
|
Posted: 05 Feb 2014 at 7:33am |
It worked perfect thank !!!
however when I want to display next date after fulfilled at the group level which means in this case it is 1/11/2014
I tried the same formula with little changes
@fulfilled
IF STATUS ="fulfilled" THEN next (DATE )
but when I use summary formula its giving me an error
@fulfilled2
maximum(@fulfilled,id)
It says "@fulfilled " can't be summarized.
|
|
chand
|
|
IP Logged |
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|
Posted: 05 Feb 2014 at 9:09am |
|
this is kind of tricky
you could try
formula like
if (DATE ) = @fulfilled2 then next((DATE))
then you add this new formula to record sort expert in
descending order place it into group footer and it should show the correct value.
|
|
IP Logged |
|
achandana01
Groupie
Joined: 20 Jul 2011
Online Status: Offline
Posts: 59
|
Posted: 05 Feb 2014 at 10:51am |
Here are the formulas which I used
@fulfilled
IF STATUS ="fulfilled" THEN next (DATE )
@fulfilled2
maximum(@fulfilled,id)
@next_date
if (DATE ) = @fulfilled2 then next((DATE))
when I put @nextdate in GF its displays as blank and more over I am not able to see @next_date formula in record sort expert.
|
|
chand
|
|
IP Logged |
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|
Posted: 05 Feb 2014 at 11:09am |
|
try this
@fulfilled
IF STATUS ="fulfilled" THEN DATE
@fulfilled2
maximum(@fulfilled,id)
@next_date
if (DATE ) = @fulfilled2 then next((DATE))
|
|
IP Logged |
|
achandana01
Groupie
Joined: 20 Jul 2011
Online Status: Offline
Posts: 59
|
Posted: 05 Feb 2014 at 11:26am |
I am sorry I tried the same formula's earlier but didn't type correctly and its blank ...... is there another way to try it though ?
Thanks for your help!!!
|
|
chand
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Feb 2014 at 11:34am |
|
is the status of the item you wish to display always = 'closed' as in your example? Or are some closed but not fulfilled, or others fulfilled and not closed?
|
|
IP Logged |
|
achandana01
Groupie
Joined: 20 Jul 2011
Online Status: Offline
Posts: 59
|
Posted: 06 Feb 2014 at 2:42am |
its not always closed it could be some thing else and moreover 'Closed' is not always last record.The above is just an example. Lets look at different scenerio
| ID |
Line |
Status |
Date |
| 100 |
1 |
Started |
1/1/2014 |
| 100 |
2 |
Inprogress |
1/2/2014 |
| 100 |
3 |
sent |
1/3/2014 |
| 100 |
4 |
fulfilled |
1/4/2014 |
| 100 |
5 |
Received |
1/5/2014 |
| 100 |
6 |
sent |
1/6/2014 |
| 100 |
7 |
fulfilled |
1/10/2014 |
|
|
|
| 100 8 Auth. sent 1/11/2014
100 9 Closed 1/12/2014
Edited by achandana01 - 06 Feb 2014 at 2:43am
|
|
chand
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Feb 2014 at 4:36am |
try using a running total with an evaluate formula
name = whatever you want
field to summarize=date
type=maximum
evaluate= use a formula
previous(status)='fulfilled'
reset = on change of group
|
|
IP Logged |
|
|
|
