Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Help with Ageing formulae

Posted on 2013-07-01
Medium Priority
202 Views
I have created a formulae which I know does not work

if isnull ({WO.SCHEDSTARTDATE}) then 'No Shed Date' else
if ({WO.SCHEDSTARTDATE}) < currentMonth then 'Future Months Requirement' else
if ({WO.SCHEDSTARTDATE}) in currentmonth then 'Due this Month' else
if ({WO.SCHEDSTARTDATE}) in LastFullMonth then 'Due Last Month' else

Can someone correct the above for me
Gordon
0
Question by:GiaHughes
[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
• 4
• 3
• 2

LVL 101

Expert Comment

ID: 39290379
For a future requirement the Scheduled Start Date would be GREATER THAN today.

if isnull ({WO.SCHEDSTARTDATE}) then 'No Shed Date' else
if ({WO.SCHEDSTARTDATE}) in currentmonth then 'Due this Month' else
if ({WO.SCHEDSTARTDATE}) in LastFullMonth then 'Due Last Month' else
if ({WO.SCHEDSTARTDATE}) > currentMonth then 'Future Months Requirement' else

What isn't working?

mlmcc
0

LVL 77

Accepted Solution

peter57r earned 1000 total points
ID: 39290380
if isnull ({WO.SCHEDSTARTDATE}) then 'No Shed Date'
else
if {WO.SCHEDSTARTDATE} > dateserial(year(currentdate), month(currentdate)+1,0)then 'Future Months Requirement'
else
if {WO.SCHEDSTARTDATE} >= dateserial(year(currentdate), month(currentdate),1) then 'Due this Month'
else
if {WO.SCHEDSTARTDATE} in LastFullMonth then 'Due Last Month'
else
"Before Last Month"
0

Author Comment

ID: 39290452
Hi mimcc/peter67r

Good to hear from you
I think the solution peter has given me works
How can i make the report order the heading
No Shed Date
Future Months Requirement
Due This Month
Older than last month

Rather than in alpha order
Gordon
0

LVL 77

Expert Comment

ID: 39290551
If you are grouping on this field then you can use the 'Specific Order' option in the Group dialog to set the group order.

Or you could just prefix the text with a letter to force a sequence..

A. No Shed Date
B. Future Months Requirement
C. Due This Month
D. Due Last Month
E. Older than last month
0

Author Comment

ID: 39290563
Hi Peter57r
Where do I find the Group dialog so that i can specify the order?

Gordon
0

LVL 101

Expert Comment

ID: 39290613
Right click on the group header in the left margin
Click CHANGE GROUP
Click the dropdown on order
Choose SPECIFIED ORDER
A new tan will appear and you can specify the order

mlmcc
0

Author Comment

ID: 39290651
Hi mimcc
The information is presented in a cross tab which is in a sub-report
so the cross tab is currently shown in the report header of the sub report
hope this makes sense, so I currently do have a Change group option
Any suggestions

Gordon
0

LVL 101

Assisted Solution

mlmcc earned 1000 total points
ID: 39290666
You can do the same thing in the cross tab

Edit the subreport
Right click the cross tab in the upper left corner
Click CROSS TAB Expert
Select the field in the row or column block
Click CHANGE GROUP
Set the order to SPECIFIED ORDER

mlmcc
0

Author Closing Comment

ID: 39290698
Always excellent from mimcc and peter57r
0

## Featured Post

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
###### Suggested Courses
Course of the Month9 days, 8 hours left to enroll