Solved

# Creating an Aging View ??

Posted on 2003-02-19
Medium Priority
251 Views
I need ot create a view that displays documents by Status that are categorized by aging days. For instance a status of open for the 4 categories:  > 20 days, 11 to 20, 6 to 10 and 2 to 5.
I have created a view sorted by Status, I then created another column that I am looking to possible place a formula in but I'm stuck as to the code ?!?!?!
Thanks !
Paul
0
Question by:pratigan
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 5
• 3
• +2

LVL 5

Accepted Solution

AndrewJayPollack earned 200 total points
ID: 7981607
unfortunately, that will make the view "time sensitive" meaning it will always show that it needs a refresh.

Lets assume you have a field on the form called "OpenDate" which is a date field, created when the status is first set to "Open" and another field called "Status" that has the value "OPEN" or some other value.

There are lots of ways to code this, I do it this way because its easy to read, rather than using a realy complex @If() with multiple cases.  This escalates, each value overwriting the other until it not longer passes the test for time length, then keeps that value.

Finally, it tests to see if the document is still open, that way you can use this category within other categories and it won't produce wierd results.

Column formula:

N1 := @If( @Adjust(OpenDate; 0; 0; 2; 0; 0; 0) > @Date(@now) ; "Open 2+ Days" ; "Open 2 or fewer days");

N2 := @If( @Adjust(OpenDate; 0; 0; 5; 0; 0; 0) > @Date(@now) ; "Open 5+ Days" ; N1 );

N3 := @If( @Adjust(OpenDate; 0; 0; 10; 0; 0; 0) > @Date(@now) ; "Open 10+ Days" ; N2 );

N4 := @If( @Adjust(OpenDate; 0; 0; 20; 0; 0; 0) > @Date(@now) ; "Open 20+ Days" ; N4 );

@If( Status = "OPEN" ; N4 ; "No longer open");
0

LVL 9

Expert Comment

ID: 7981867
Cool Aj ! I have a different version as you said.  If required i could send him.
0

LVL 13

Expert Comment

ID: 7982062
Andrew, the refresh button won't show when you use
@TextToTime("Today")
@Now

;-)
0

LVL 5

Expert Comment

ID: 7988266
yea, but then the view isn't up to date unless he manually refreshes.  He does want an AGING view, so I presume that its date sensitive and he wants it to stay up to date.
0

LVL 4

Author Comment

ID: 7989361
Will This show headings ??  The view should like something like this:
>20 days, then the list of documents, then the total number.
11 - 20 Days, then the list of documents, then total
6 - 10 Days, then list documents, then total
2 - 5 Days, then documents, then totals.
The Status would be categorized twistie, then sub Days listings >20,11-20, 6-10, 2-5, categorized twistie then the actual documents then a total. ???
Not sure how to set this up ??
Thanks !!
:)
0

LVL 4

Author Comment

ID: 7993441
Hello Andrew,
In reviewing the formulas you have established, I think their backwards.
OpenDate is thesame as a field I have called creationDate.  Therefore to test for aging over 20 days the formula should be something like:
@If(@Adjust(@Date(@Now); 0; 0; -20; 0; 0; 0) > CreationDate) then the document is greater than 20 days old. Am I thinking this correctly ???
I'm wondering how to have all of the formulas in 1 view.  I can create 4 different view, one for each aging period, nut I wanted to have 1 view with all four different aging periods and have the periods as the twistie category ??!!
<:-|
0

LVL 4

Author Comment

ID: 7993508
Ok... Here's what I have done:  I have created 4 views, 1 for each aging period.  The formula for the first 2 are:
>20 days the view selection formula is:
SELECT ((Form = "CSC Incident report") &
(@Adjust(@Date(@Now); 0; 0; -20; 0; 0; 0) > Creation_Date_1)).
The second between 11 - 20 I have the formula as:
SELECT ((Form = "CSC Incident report") &
((@Adjust(Creation_Date_1; 0; 0; 11; 0; 0) <= @Date(@Now)) &
(@Adjust(Creation_Date_1; 0; 0; 20; 0; 0) >= @Date(@Now))) .
Am I on the right track with this ???  Also, this will work, but I wanted to have them on 1 view.
?!!?!

0

LVL 5

Expert Comment

ID: 7995619
the concept is right, Pratigan.  No reason they can't be in the same view.  I'll look more closely when I'm home later tonight.  (I'm mobile right now in a hospital waiting room -- nothing critical).
0

LVL 13

Expert Comment

ID: 7999499
Not another car accident I hope!?
0

LVL 5

Expert Comment

ID: 8000717
Just killing time at the hospital.  Waiting, waiting, waiting.   My wife had an appendectomy.  I've got this cool new 3G cellular "express net" card in my laptop.  It has a tiny little antenae that nobody notices, and I have two batteries to give me nearly 6 hours of live time.  Now I'm TRULY mobile.

Pratigan, I'm sorry I haven't had time to put that into a single view.  I'll try to get to it by monday afternoon.  The info is all there, it just needs a little more polish on it to make it clear.
0

LVL 5

Expert Comment

ID: 8000724
btw: With Barb's surgery, I couldn't go as a member of the team for training, but I did take the kids to a training burn today.  Someone donated an old house that needed to be taken down, so we used it for practice:

http://www.thenorth.com/northern.nsf/html/wburn

--AP
0

LVL 4

Author Comment

ID: 8008285
Great Pictures..... Looked like a nice house.
Thank you !
Paul
:-)
0

LVL 4

Author Comment

ID: 8130954
Hello All,
i have resolved this question with my previous posting.  I will still be granting points however for your efforts.  I'm going to grant 50 points to Andrew for his input and time.  I hope this is acceptable.  Thank you !
Paul
:)
0

LVL 9

Expert Comment

ID: 8130977
And me ?
0

Expert Comment

ID: 8130986
Points reduced from 100 to 50 per request

SpideyMod
Community Support Moderator @Experts Exchange
0

LVL 4

Author Comment

ID: 8131043
Arun... you are a point junky... I will be having points for you in my other posting with the 2nd notes error..
Thanks !!
:-)
0

LVL 9

Expert Comment

ID: 8131114
Thats cool....
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem "Can you help me recover my changes? Â I double-clicked the attachment, made changes, and then hit Save before closing it. Â But when I try to re-open it, my changes are missing!" Â  Â Solution This solution opens the Outlook Secure Temp Foldâ€¦
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as formâ€¦
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on tâ€¦
###### Suggested Courses
Course of the Month8 days, 12 hours left to enroll