Skip to Content
Author's profile photo Anshu Lilhori

Status of Tickets based on their Dates

Scenario:

Sometimes we may get the requirement to find the status of ticket based on their created on and closed date.This document can be applicable for any object which has some validity period and we need to find the status of it based on any date.

Lets say for eg:

Ticket Created on Closed Status
1001 20130101 20130115 CLOSED

So now if user enter any date in between those two dates then we need to get the status of the same.

The approach which we can follow to achieve the same will be sharing with you with all the steps and screenshots.

Data Sheet:

8.JPG

Data Loaded in DSO:

9.JPG

Step 1–Go to  BEx Query Designer–Drag and Drop Created on date in Characteristic Restrictions panel–Right click on Created date—Restrict–Show–Value ranges—Less than or equal to–Choose the variable 0I_DAYWT (User input variable).

1.JPG

Step 2Now drag and drop Closing Date in Characteristic Restrictions panel–Right click on Closing date–Restrict–Variable–Create new variable.Refer the screenshot for details.

2.JPG

Hit the Replacement Path tab and give the details as mentioned in below screenshot

3.JPG

Now Save this variable.

Restrict the Closing date with the newly created variable as shown in below screenshot.

4.JPG

Users also need to see the date which has been entered by them on Selection screen in Query Columns as well.So for that our approach will be explained in furhter steps.

Step 3Go to Columns panel in query designer create a new formula–Under Available Operands–Formula variable–Right click–New variable.Refer the Screenshot for reference.

5.JPG

Hit the Replacement Path tab and give the details as mentioned in below screenshot

6.JPG

Hit the Currency/Unit tab and give the details as mentioned in below screenshot.

7.JPG

Save this Variable and use it in your formula.

Query Format

11.JPG


Variable Screen:

12.JPG

Query Output:

14.JPG

So you can see based on the input date we got the status of tickets and also we got the date entered by user on selection screen in User Input column.

Hope this will be helpful.

Assigned Tags

      63 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Krishna Chaitanya
      Krishna Chaitanya

      Hi Anshu,

      Thanks for sharing this to all of us.  Actually I implemented the same in my previous project.

      Thank you once again for remembering all those things.

      🙂 🙂 🙂 🙂 🙂

      Regards,

      Krishna Chaitanya.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Krishna,

      Its always great feeling to receive the first comment on the blog.. 🙂 ..This document will definitely come handy in future so thought of documenting it.

      Much appreciated your feedback.

      Regards,

      AL

      Author's profile photo CH Raman
      CH Raman

      Like this way needed more docs on Bex category. Really good one and sure may come handy soon. Thank You anshu for making and sharing with us.

      Regards

      Raman

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Raman for your valuable feedback and continuous support and encouragement.

      Much appreciated.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hi Anshu,

      Good thought and Nicely presented with screen shots how to implement. 🙂 🙂

      Thanks For Sharing with us. 😉 😉 😉

      Regards,
      Giri

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Giri..Glad that you liked it.. 🙂 ..Your smileys really make me 🙂

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hi Anshu,

      Created on and Closing Date restrictions will work together as AND condition right.

      The variable created in Step 2 and Step 3 are same or different?

      Regards,

      Dilip.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Hi Dilip,

      If you look closely at those variables then you will notice one is formula variable and other characteristic variable..

      Regards,

      AL

      Author's profile photo Satendra Mishra
      Satendra Mishra

      NIce information anshu.

      🙂

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Satendra..for your feedback.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Ok the second one is Formula variable with Replacement path, fine

      Thanks,

      Dilip.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Yes dilip correct..

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K

      Hi Bex Hero,

      Gr8 Post. Appreciate your efforts in making nicely and informatively 🙂 🙂

      Regards,

      Suman

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Hi Suman,

      Thanks for your valuable comments..you do not know that your blogs inspire me to make such blogs.. 🙂

      Regards,
      AL

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K

      I am inspired by others blogs. Your are inspired by my blogs. It's all continuous process in SCN 🙂

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Yes its a continuous process which goes on and should keep going.. 🙂

      Author's profile photo Sandeep a
      Sandeep a

      Hi Anshu

      Thanks For Sharing with us..

      Regards

      Sandeep

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Sandeep.

      Regards,

      AL

      Author's profile photo Abdullah Qureshi
      Abdullah Qureshi

      Very Nice blog Anshu.

      Thanks for sharing..

      Regards,

      Abdullah

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Abdullah.for your feedback.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Its very nice document.

      Regards,

      Sushant

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Sushant..for your feedback

      Regards,

      AL

      Author's profile photo Martin Grob
      Martin Grob

      Nice blog Anshu!

      Martin

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Martin for your feedback..

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hi Anshu,

      Nice work !!

      I will implement this approach in my requirement also which i did via writing long CMOD codes in i_step = 2 mode .

      To add further, my requirement also extends to having month as a general input rather than date . So i am trying to figure out an approach for that also.

      Regards,

      Ashu

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Hi Ashutosh,

      Thanks for your feedback..So you are saying that instead of date you will have month or both.

      I believe by replacing the date with month in my approach it should suffice your requirement.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      I am not completely sure of this requirement at this moment as it needs further clarification from the management .... but what i am guessing is that .... instead of a general date .... i will have a general month as an input.

      And then we need to check whether the intervals of created on and closing date lie partially within that month or completely. And display for example respective ticket numbers .

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      I will also be little skeptical unless we do not have the complete requirement with the proper data.As this makes easy to analyze and get some concrete solution.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member
      Yea sure....even I am waiting for the correct inputs !!
      Author's profile photo Kamal Mehta
      Kamal Mehta

      Hi Anshu,

      Nice one exploring the potential of Formula variables especially.

      Sees lots of yous interest in Bex area.

      Thanks

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Yes Kamal very true..I am digging out all possibility to avoid codes in certain occasion if your requirement is straightforward..

      Soon will come up with more blogs in all areas.

      Regards.

      AL

      Author's profile photo Kamal Mehta
      Kamal Mehta

      Hmmn.

      We are waiting then.

      Thanks

      Author's profile photo Former Member
      Former Member

      very well explained. thanks for the information.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks chadra..Really appreciate your comments.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Really Very nice document, Keep it up Anshu... 🙂

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Mithun for your valuable feedback.

      Much appreciated.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Good Presenation for scenario based on Replacement path.Helpful one

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Karthick..Really appreciate your feedback.

      Regards,

      AL

      Author's profile photo Prashanth konduru
      Prashanth konduru

      Anshu ... You rock... Really Appreciate your effort and well drafted the document...

      Thanks for sharing ... Keep posted.

      Rated the doc.

          

      Regards

      KP

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks KP..Your comments really elated me.. 🙂 Yes will try to come up with some new blogs soon.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Really useful blog. Thanks

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Raghvendra for your feedback..

      Regards,

      AL

      Author's profile photo Praveen Kumar
      Praveen Kumar

      nice Blog specially very descriptive one

               

      Regards

      Praveen Kumar   

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Praveen for your comments.

      Regards,

      AL

      Author's profile photo Eduardo Rezende
      Eduardo Rezende

      Nice blog.
      Thanks for sharing.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Glad that you liked the blog..Eduardo.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Thanks Anshu for putting it nicely with all the screenshots. It will help many people.

      Small suggestions:

      1. You could make it further better by putting few more lines explaining "how it works". I know its obvious for BW consultants but it might help newbies.

      "How it Works" section should contain 3/4 lines. saying how the user's input is taken into account for selection of data and displaying the last column.

      2. You could also add some more tags. "date calculation", "selection based on two dates".

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Sujit for your feedback and will definitely try to consider the points you mentioned.

      I learnt a lot from your replies when you were active contributor and also liked the approaches you used to address the issues.Hope to see you again contributing actively.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Superb !! . It would be really great if we get more documents on BEx like this 🙂

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Sanyukta Shrivastava for your feedback..In my blogs and documents i mostly try to share some real time issues and scenarios which people faces while working on a project..

      Regards,

      AL

      Author's profile photo Vijay Chandra.R
      Vijay Chandra.R

      Dear Anshu....Great piece of information....

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks a lot vijay for your feedback.

      Regards,

      AL

      Author's profile photo Rishabh S
      Rishabh S

      Nice doc...very helpfull

      thanks Anshu for sharing with us.

      Regards,

      Rishabh

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Rishab for your comments.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      It was really nice Anshu.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks for your feeback.Much appreciated.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hello Anshu,

      Nice document, I am sure the techinique you implemented here would definetely come in handy to everyone working with BEx.

      Thanks for sharing !

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks for your feedback..Infact i had same requirement in one of my projects i ended up achieving it with the help of code and then eventually realized that this can also be possible with the help of variable operators.. 😉

      Regards,

      AL

      Author's profile photo Wasem Hassan
      Wasem Hassan

      Hi Anshu,

      its very good one doc.

      Thanks for sharing...

      Keep it up 🙂 🙂 🙂

      Regards

      Wasem

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Good to know,that you liked it. 🙂

      Regards,

      AL

      Author's profile photo Ganesh Bothe
      Ganesh Bothe

      Hi Anshu,

      Useful information thanks for sharing 🙂 .

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks ganesh for your feedback..You can like or rate the content i would really appreciate it.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hi Anshu,

      very useful document,nice explanation with screen shots.

      Regards,

      J.Sakthikumar