Skip to Content
author's profile photo Former Member
Former Member

>30 days and incomplete

Post Author: EarthMover

CA Forum: Formula

I have CR X Pro and my question is this: Is there a way to create a list that will give me tickets that are currently greater than 30 days old, and have not been completed. Here's my data: A new ticket has an OPUP condition and a completed ticket has a COMP condition, so if a ticket has only the OPUP condition and no COMP condition as of right now, then it has yet to be completed. My problem is that I can't just select the ones with only the OPUP condition, which makes it impossible to tell if they've been completed or not. The first two lines are an example of when the ticket was created and when it was completed. The last line of the data is an example of a ticket that has yet to be completed and is older than 30 days.

Any help would be greatly appreciated.

EQP_NO

PROB_DESC_TXT

ORD_STAT_STRT_TS

SVC_ORD_COND_CD

M 00000711

PM 725

10/12/2007 2:17:15 AM

OPUP

M 00000711

PM 725

10/12/2007 4:47:33 PM

COMP

M 00001871

PM 125

9/28/2007 2:37:15 AM

OPU

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Nov 22, 2007 at 07:33 PM

    Post Author: yangster

    CA Forum: Formula

    there are a number of ways you can tackle this isuea question though can a ticket be closed then re opened or once its closed its always closed?

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 26, 2007 at 01:39 PM

    Post Author: EarthMover

    CA Forum: Formula

    Once a ticket is closed it cannot be re-opened. These particular tickets are generated by the system every friday, and are PM tickets ( Planned Maintenance )

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 26, 2007 at 05:44 PM

    Post Author: yangster

    CA Forum: Formula

    to do this purely in crystal you need to look at the begin and end dates by looking at multiple linesgroup by your equipno and any other unique identifier needed@date_diffdatetimevar begin_date := nthsmallest(1, date, equip_no);datetimevar end_date := if nthlargest(1, date, equip_no) = nthsmallest(1, date, equip_no) then currentdatetime else nthlargest(1, date, equip_no);datediff ("d", begin_date, end_date);this will tell you how many days are between when the ticket was created and the close date (or current date)you can then do further filtering by looking at the status of the last entry to see if it was closed and supressing all records you don't want to see

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 03, 2007 at 03:01 PM

    Post Author: EarthMover

    CA Forum: Formula

    The formulas worked great, but now I'm having trouble viewing only those with a date diff of 30 days or greater. I'm pretty sure it has to do with the fact that I used problem_desc instead of equip_no for my last field in the formula. I did this so it would find the first and last transaction for that particular problem, which is what I need. Otherwise, with equip_no in the formula, it will return the first ticket put in and last ticket completed for the equipment, which could be a year apart or better, and useless information.

    So my question is: now that I'm able to tell which ones are >30 and incomplete, how do I show only those instances?

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 03, 2007 at 04:37 PM

    Post Author: yangster

    CA Forum: Formula

    simply suppress the values that do not meet your > 30 criteriaso click on section detail, hit x2, and put in the basicif @your_formula > 30 then false else trueso any date that is greater then 30 days it will show up else it will be supressed

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.