| Author |
Message |
AndreasI
Newbie
Joined: 29 Jul 2009
Location: Australia
Online Status: Offline
Posts: 8
|
   Topic: Formula to detemine month
Posted: 06 Sep 2009 at 11:06pm |
I have been asked to develop some reports grouped by month. The field I am using (View_Incidents.Open_Date) to base the month on is in the datetime format of DD MON YYYY HH:MM:SS, with an example string being 01 Apr 2009 01:00:59.
I tried using an if-then-else formula to attribute the month string based on between values, but that failed to return all months. Since I have yet to select a range I know the strings exist, despite not being handled by my (probably wrong) formula.
So now I need a little help with writing one of those nifty little string search formulae I've seen people on here use...one that looks at the MON part only, then depending on that string, attributes the month for grouping. Can't wait for RD210 doing it in October :)
Thanks in advance.
|
 IP Logged |
|
|
Jyothi Yepuri
Senior Member
Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
|
Posted: 06 Sep 2009 at 11:45pm |
|
Create a formula as below, group by it
MonthName(MONTH(CDATETIME(View_Incidents.Open_Date)))
All data is from current year?
HTH,
Jyothi
|
|
IP Logged |
|
AndreasI
Newbie
Joined: 29 Jul 2009
Location: Australia
Online Status: Offline
Posts: 8
|
Posted: 07 Sep 2009 at 4:32pm |
Thankyou! I presume its a version issue but I needed to amend the syntax slightly:
MonthName(MONTH(CDATETIME({View_Incidents.Open_Date})))
But the formula worked a charm and I was able to group the first report I tried it on by month.
Some reports work on just this years data (which is the case for the report I needed this formula for) and others being developed will query the entire data set.
Why do you ask?
Edited by AndreasI - 07 Sep 2009 at 4:36pm
|
|
IP Logged |
|
Jyothi Yepuri
Senior Member
Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
|
Posted: 07 Sep 2009 at 4:41pm |
|
I don't think its version issue, you should add braces for database fields in crystal which i haven't added in my example :)
about the data in same year, If you group by month and have incidents opened in 2008, 2009 then you will not get accurate data.
in that case you should group by year also. something like this
2009
Jan
Feb
...
2008
Jan
Feb
...
Jyothi
|
|
IP Logged |
|
AndreasI
Newbie
Joined: 29 Jul 2009
Location: Australia
Online Status: Offline
Posts: 8
|
Posted: 07 Sep 2009 at 6:22pm |
I assumed that would be the case. How would I code for year?
Edited by AndreasI - 07 Sep 2009 at 6:26pm
|
|
IP Logged |
|
Jyothi Yepuri
Senior Member
Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
|
Posted: 07 Sep 2009 at 6:29pm |
|
YEAR(CDATETIME({View_Incidents.Open_Date}))
try grouping by this formula
"why MONTH works on a text string date"?
Month, year both funtions require Date field
where as MonthName requires number in range 1 to 12
Jyothi
|
|
IP Logged |
|
AndreasI
Newbie
Joined: 29 Jul 2009
Location: Australia
Online Status: Offline
Posts: 8
|
Posted: 07 Sep 2009 at 7:05pm |
Wow, that was quick!
I got around the problem using 2 formula - one to convert the date:
Date({View_Incidents.Open Date})
and then one to extract the year:
but i like your approach better.
Thanks again :)
|
|
IP Logged |
|
AndreasI
Newbie
Joined: 29 Jul 2009
Location: Australia
Online Status: Offline
Posts: 8
|
Posted: 07 Sep 2009 at 7:21pm |
Grouping by Year then Month loses my Year headings between the months, but thats hardly a technical question and the grouping works well nonetheless :)
Whats my best approach for summarising this information to count the number of jobs by month/year (e.g number of jobs in Feb 2008 etc)? I tried some basic summaries but have hit the books again :)
Thanks
Edited by AndreasI - 07 Sep 2009 at 7:22pm
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 07 Sep 2009 at 7:22pm |
As another solution
convert he field as you were to a datetime
CDATETIME({View_Incidents.Open_Date})
group on that formula field and alter the grouping type to "for each month" which also keeps it per month/year.
It will also keep it in the correct sequential order unlike converting it to the monthname whcih makes it alpha order.
If you want to visually make:
g1: Year
g2: Month
Group on the formula field twice and make it per year first and per month secondly
|
|
IP Logged |
|
AndreasI
Newbie
Joined: 29 Jul 2009
Location: Australia
Online Status: Offline
Posts: 8
|
Posted: 07 Sep 2009 at 7:27pm |
Brilliant! These solutions are exactly what I needed, thanks gentlemen!
|
|
IP Logged |
|
|
|
