Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Display 13 previous weeks from current date. Crystal Repots 8.5 1
- Thread starter ThaGooch
- Start date
Hello,
I don't have a solution, but I am haing a similar problem so there is no point in starting a new thread over it.
I have a report that uses a date range to get certain data. I made a total field of all data at teh bottom of my report, and now want to make a total-previous field which would have everything from the beginning of the year up until the beginning of the current range specified in my report. Is there a function that can do this? I found one called LastMonth but it only does a month's worth of data and it uses today's date, not the date range I specified in my report.
Comments, questions and rude remarks are welcome! :O)
Toxodont
I don't have a solution, but I am haing a similar problem so there is no point in starting a new thread over it.
I have a report that uses a date range to get certain data. I made a total field of all data at teh bottom of my report, and now want to make a total-previous field which would have everything from the beginning of the year up until the beginning of the current range specified in my report. Is there a function that can do this? I found one called LastMonth but it only does a month's worth of data and it uses today's date, not the date range I specified in my report.
Comments, questions and rude remarks are welcome! :O)
Toxodont
Assuming the following:[ol][li]You want data from the current week[/li][li]You want data from the previous twelve calendar weeks[/li][/ol] you could use the following formula:
//Switch statement used to pass data to the database
//Statement will return up to 13 calendar weeks of data, inclusive of the current week
{table.datefield} In
Switch
(
DayofWeek(CurrentDate) = 1,(CurrentDate - 84) To CurrentDate,
DayofWeek(CurrentDate) = 2,(CurrentDate - 85) To CurrentDate,
DayofWeek(CurrentDate) = 3,(CurrentDate - 86) To CurrentDate,
DayofWeek(CurrentDate) = 4,(CurrentDate - 87) To CurrentDate,
DayofWeek(CurrentDate) = 5,(CurrentDate - 88) To CurrentDate,
DayofWeek(CurrentDate) = 6,(CurrentDate - 89) To CurrentDate,
DayofWeek(CurrentDate) = 7,(CurrentDate - 90) To CurrentDate
)
This statement produced the following SQL (passed to a SQL Server 2K db):
SELECT
table."field1", table."datefield"
FROM
"database"."dbo"."table" table
WHERE
table."datefield" >= {ts '2003-03-16 00:00:00.00'} AND
table."datefield" <= {ts '2003-06-12 23:59:59.00'}
//Switch statement used to pass data to the database
//Statement will return up to 13 calendar weeks of data, inclusive of the current week
{table.datefield} In
Switch
(
DayofWeek(CurrentDate) = 1,(CurrentDate - 84) To CurrentDate,
DayofWeek(CurrentDate) = 2,(CurrentDate - 85) To CurrentDate,
DayofWeek(CurrentDate) = 3,(CurrentDate - 86) To CurrentDate,
DayofWeek(CurrentDate) = 4,(CurrentDate - 87) To CurrentDate,
DayofWeek(CurrentDate) = 5,(CurrentDate - 88) To CurrentDate,
DayofWeek(CurrentDate) = 6,(CurrentDate - 89) To CurrentDate,
DayofWeek(CurrentDate) = 7,(CurrentDate - 90) To CurrentDate
)
This statement produced the following SQL (passed to a SQL Server 2K db):
SELECT
table."field1", table."datefield"
FROM
"database"."dbo"."table" table
WHERE
table."datefield" >= {ts '2003-03-16 00:00:00.00'} AND
table."datefield" <= {ts '2003-06-12 23:59:59.00'}
Assuming you are using Crystal 8 or better:
{YourDateField} in CurrentDate to Dateadd("ww",-13,CurrentDate)
Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
{YourDateField} in CurrentDate to Dateadd("ww",-13,CurrentDate)
Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
-
1
- #5
synapsevampire
Programmer
Does 13 weeks mean from today, or the last 13 full weeks?
If the former, dgillz solution will work by placing the formula in:
Select Report->Edit Selection Formula->Record
If you want the past 13 full weeks alter the formula to:
{Orders.Order Date} in minimum(lastfullweek) to Dateadd("ww",-13,maximum(lastfullweek))
-k
If the former, dgillz solution will work by placing the formula in:
Select Report->Edit Selection Formula->Record
If you want the past 13 full weeks alter the formula to:
{Orders.Order Date} in minimum(lastfullweek) to Dateadd("ww",-13,maximum(lastfullweek))
-k
synapsevampire
Programmer
Ooops, did that backwards:
{Orders.Order Date} in Dateadd("ww",-13,minimum(lastfullweek)) to maximum(lastfullweek)
-k
{Orders.Order Date} in Dateadd("ww",-13,minimum(lastfullweek)) to maximum(lastfullweek)
-k
dgillz and sv, that formula won't be passed to the database:-(
If performance isn't a concern, then I'd modify the formula slightly to return 13 weeks of data, including the current week, to date. This formula is certainly less verbose than mine:
//If the current week is 'week 13' then the last full week
//is 'Week 12', therefore, you only want to subtract 11
//weeks from the last full week...
{Orders.Order Date} in Dateadd("ww",-11,minimum(lastfullweek)) to CurrentDate
If performance isn't a concern, then I'd modify the formula slightly to return 13 weeks of data, including the current week, to date. This formula is certainly less verbose than mine:
//If the current week is 'week 13' then the last full week
//is 'Week 12', therefore, you only want to subtract 11
//weeks from the last full week...
{Orders.Order Date} in Dateadd("ww",-11,minimum(lastfullweek)) to CurrentDate
How about date ranges though?
I have a parameter that stores a date range (usually it ends up being a one month range). How can I specify the time between the start of the year and the start of the range?
?date is my parameters name.
Thanks
Toxodont
I have a parameter that stores a date range (usually it ends up being a one month range). How can I specify the time between the start of the year and the start of the range?
?date is my parameters name.
Thanks
Toxodont
Toxodont,
To get the time period within the same calendar year that precedes the parameter range, you can just specify:
{table.date} in Date(Year(minimum({?date})), 01, 01) to Minimum({?date})
But you need to return the entire time frame for any data referred to in the report through your record select. Since you are using the {?date} for at least one summary, you will have to expand your record select to:
{table.date} in Date((Year(minimum({?date})), 01, 01) to Maximum({?date})
Then use the previous period formula or {?date} in conditional formulas which you then summarize to get totals, as in:
if {table.date} in Date(Year(minimum({?date})), 01, 01) to Minimum({?date}) then 1 else 0// or
if {table.date} in {?date} then 1 else 0
-LB
To get the time period within the same calendar year that precedes the parameter range, you can just specify:
{table.date} in Date(Year(minimum({?date})), 01, 01) to Minimum({?date})
But you need to return the entire time frame for any data referred to in the report through your record select. Since you are using the {?date} for at least one summary, you will have to expand your record select to:
{table.date} in Date((Year(minimum({?date})), 01, 01) to Maximum({?date})
Then use the previous period formula or {?date} in conditional formulas which you then summarize to get totals, as in:
if {table.date} in Date(Year(minimum({?date})), 01, 01) to Minimum({?date}) then 1 else 0// or
if {table.date} in {?date} then 1 else 0
-LB
lbass, I tried your suggestion and it worked sort of. I did get the number I was looking for, but when I change my record selection it fudges up the other values in my report.
Going from this:
//New Construction
{PERMIT.TYPEOCC} = ["Single Family" , "Two Family" , "Multi-Family" , "Commercial" , "Industrial" , "Institutional" , "Other" , "Mobile Home" , "Recreational" , "Agricultural"]
and {PERMIT.MUNUNIT} = ["New Glasgow" , "Pictou" , "Stellarton" , "Trenton" , "Westville"]
and {PERMIT.DATEBLDG} = {?Date}
and {PERMIT.APP} = ["Conventional Building" , "Private Garage" , "Pre-MFGED Building"]
to this:
//New Construction
{PERMIT.TYPEOCC} = ["Single Family" , "Two Family" , "Multi-Family" , "Commercial" , "Industrial" , "Institutional" , "Other" , "Mobile Home" , "Recreational" , "Agricultural"]
and {PERMIT.MUNUNIT} = ["New Glasgow" , "Pictou" , "Stellarton" , "Trenton" , "Westville"]
and {PERMIT.DATEBLDG} in Date((Year(minimum({?date})), 01, 01) to Maximum({?date})
and {PERMIT.APP} = ["Conventional Building" , "Private Garage" , "Pre-MFGED Building"]
You can probably see how the data returned to the report now is not the same as what it was returning before, which means many of my summaries in this report (manual crosstab) don't get the correct value.
I guess it might end up boiling down to me going through the summaries and specifying that {PERMIT.DATEBLDG} = {?date} now that the record selection may need to be modified. Is there any way to avoid this, seeing as I have MANY fields that would require updating... Is there a way to select an idividual field and change it's record selection specifically?
Thanks for all your help.
Toxodont
Going from this:
//New Construction
{PERMIT.TYPEOCC} = ["Single Family" , "Two Family" , "Multi-Family" , "Commercial" , "Industrial" , "Institutional" , "Other" , "Mobile Home" , "Recreational" , "Agricultural"]
and {PERMIT.MUNUNIT} = ["New Glasgow" , "Pictou" , "Stellarton" , "Trenton" , "Westville"]
and {PERMIT.DATEBLDG} = {?Date}
and {PERMIT.APP} = ["Conventional Building" , "Private Garage" , "Pre-MFGED Building"]
to this:
//New Construction
{PERMIT.TYPEOCC} = ["Single Family" , "Two Family" , "Multi-Family" , "Commercial" , "Industrial" , "Institutional" , "Other" , "Mobile Home" , "Recreational" , "Agricultural"]
and {PERMIT.MUNUNIT} = ["New Glasgow" , "Pictou" , "Stellarton" , "Trenton" , "Westville"]
and {PERMIT.DATEBLDG} in Date((Year(minimum({?date})), 01, 01) to Maximum({?date})
and {PERMIT.APP} = ["Conventional Building" , "Private Garage" , "Pre-MFGED Building"]
You can probably see how the data returned to the report now is not the same as what it was returning before, which means many of my summaries in this report (manual crosstab) don't get the correct value.
I guess it might end up boiling down to me going through the summaries and specifying that {PERMIT.DATEBLDG} = {?date} now that the record selection may need to be modified. Is there any way to avoid this, seeing as I have MANY fields that would require updating... Is there a way to select an idividual field and change it's record selection specifically?
Thanks for all your help.
Toxodont
woooo~ ok
I have a report and here is my record selection.
//New Construction
{PERMIT.TYPEOCC} = ["Single Family" , "Two Family" , "Multi-Family" , "Commercial" , "Industrial" , "Institutional" , "Other" , "Mobile Home" , "Recreational" , "Agricultural"]
and {PERMIT.MUNUNIT} = ["New Glasgow" , "Pictou" , "Stellarton" , "Trenton" , "Westville"]
and {PERMIT.DATEBLDG} = {?Date}
and {PERMIT.APP} = ["Conventional Building" , "Private Garage" , "Pre-MFGED Building"]
Now I need to make a total in my report of data that comes before my data I get using the above record selection. IE: I might make a report for the month of February, so {?date} would have the range of 02/01/2003 - 02/28/2003. The problem arises when I want to make a year to date total. How can I make a year to date total when my record selection is only pulling information for Feb.?
The report I am doing is a manual crosstab, so most of the fields rely on the record selection data. If I change the information that comes in, then my summaries will all be fudged up because they are no longer getting the same data. IE: Instead of one month of data, they will get the whole year etc.
So will I have to change my record selection in order to get the data I need, or is there a way to make some sort of a formula field that will get the past months data?
I have a tingling feeling which is telling me that I need to change my record select and then all my fields in my report.![[evil]](/data/assets/smilies/evil.gif "[evil] [evil]")
Toxodont
I have a report and here is my record selection.
//New Construction
{PERMIT.TYPEOCC} = ["Single Family" , "Two Family" , "Multi-Family" , "Commercial" , "Industrial" , "Institutional" , "Other" , "Mobile Home" , "Recreational" , "Agricultural"]
and {PERMIT.MUNUNIT} = ["New Glasgow" , "Pictou" , "Stellarton" , "Trenton" , "Westville"]
and {PERMIT.DATEBLDG} = {?Date}
and {PERMIT.APP} = ["Conventional Building" , "Private Garage" , "Pre-MFGED Building"]
Now I need to make a total in my report of data that comes before my data I get using the above record selection. IE: I might make a report for the month of February, so {?date} would have the range of 02/01/2003 - 02/28/2003. The problem arises when I want to make a year to date total. How can I make a year to date total when my record selection is only pulling information for Feb.?
The report I am doing is a manual crosstab, so most of the fields rely on the record selection data. If I change the information that comes in, then my summaries will all be fudged up because they are no longer getting the same data. IE: Instead of one month of data, they will get the whole year etc.
So will I have to change my record selection in order to get the data I need, or is there a way to make some sort of a formula field that will get the past months data?
I have a tingling feeling which is telling me that I need to change my record select and then all my fields in my report.
Toxodont
Yes, you must include the entire date range in your record selection formula. To limit dates in the manual crosstab, create formulas like:
if month({table.date}) = 2 then {table.amt}
Or substitute "then 1" if you are doing counts, not sums. For calendar year to date:
if year(table.date} = year(Currentdate} then 1 //etc.
-LB
if month({table.date}) = 2 then {table.amt}
Or substitute "then 1" if you are doing counts, not sums. For calendar year to date:
if year(table.date} = year(Currentdate} then 1 //etc.
-LB
- Status
Similar threads
- Locked
- Question