Date Range type Formula for a VarChar field

[boatie]

Hello Gurus,

I have a perplexing little formula to write that I am at a roadblock with.

I am constructing a report where I need to provide the sales figures for a definable week (easy part) as well as column for each of the past 13 weeks sales.... plus another column for the current Financial YTD sales.

I have nutted out that I need to join a separate instance of the table back to the original reference table (named STATITEM).... makind these tables STATITEM_1, STATITEM_2 etc etc.

However I do not have the foggiest clue on how to construct the formualae to take the STATITEM fields data (eg 2003.02) and regress it so that the report calculates STATITEM_1 to be (2003.01)...... as well as the YTD.

Help me Obi-Wan you are my only hope!

 

[synapsevampire]

I think that you may be overcomplicating this.

When posting, do the folks here a favor and share your version of Crystal, type and version of database, example data and expected output. This sort of posting leaves a good deal to the imagination.

If you have a date which is stored as a varchar, you should first take a razor sharp egg beater to your DBA's brain, not that it should make much difference

You can use a SQL Expression (if you have a recent version of Crystal) to CAST the varchar date to a real date. Since you didn't share the database type and version, nor what format it's stored in, I'll provide a best case example based on SQL Server:

cast('2003-12-31 15:12:15' as datetime)

Replacing your datetime field for the datetime above in quotes (don't use the above quotes in yours)

Once that is done, you can easily extract data for the current year to date, adding to that the last 13 weeks, as the last 13 weeks may fall into the previous year.

Create a formula to check for the last 13 weeks worth of data, I'll assume that this means starting on a Monday:

//@last13weeksstartdate
if dayofweek(currentdate) = 1 then
dateadd("d",-13,currentdate)
else
dateadd("d",-13,currentdate-(dayofweek(currentdate)+1))

Use a record selection formula (Report->Edit Selection Formula->Record) akin to:

(
If Year(currentdate) <> year(@last13weeksstartdate) then
{table.date} >= @last13weeksstartdate
else
if Year(currentdate) = year(@last13weeksstartdate) then
cdate(year(currentdate),month(currentdate),day(currentdate))
)

Now you have all of the data from the beginning of the year, or the past 13 weeks, whichever came first.

There are many ways to approach the next step, I'll use Running Totals as they're probably the simplest to understand:

Insert->Field Object->Right Click Running Totals and Select New

Name it 1 week old
Place the field to summarize in field to summarize qand select sum for type of summary

Select Evaluate use a formula and enter something like:
datepart(&quot;ww&quot;,{Table.Order Date}) = datepart(&quot;ww&quot;,dateadd(&quot;w&quot;,-1,currentdate))

(substituting your field names)

Now do the rest of the columns incrementing the name and the -1 in the datepart formula.

For the yeartodate, use:

{Table.Order Date} = YearToDate

Place the columns in the report footer.

Hopefully this will solve it, if not, be kind and flesh out some of the details.

-k

 

[boatie]

Sorry about that 'Vamp,

Am using Crystal 9 through an ODBC connections to an MSSQL 2000 DB. The 'Statitem' field is a VarChar 9 character data field.

cheers
B

 

[lbass]

I don't think you need to add alias tables to do this. First, use the record select to select the date range so that it reflects the longer of the YTD period or the 13-week period (if you want the previous 13 weeks regardless of whether some of them would predate the beginning of the fiscal year).

It sounds like the week of interest may change, so you might want to set it up as a discrete date parameter.

Then create a manual crosstab by using formulas like the following until you have the 13 prior week columns:

Most recent week of interest:
if {table.date} in dateadd(&quot;ww&quot;,-1,{?date}+1) to {?date} then {table.sales}

One week prior:
if {table.date} in dateadd(&quot;ww&quot;,-2,{?date}+1) to dateadd(&quot;ww&quot;,-1,{?date}) then {table.sales}

For the year to date column, create a formula like the following if the fiscal year is the calendar year:

if {table.date} in date(year({?date}), 01, 01) to {?date} then {table.sales}

If your fiscal year runs from July 1 to June 30, then use:

if {table.date} in date(year({?date}+184)-1, 07, 01) to {?date} then {table.sales}

If you are evaluating at a group level (let's say you have a group on {table.store}), then insert summaries on the above formulas and suppress the details.

Substitute your own field names, of course. That should do it.

-LB

 

[synapsevampire]

What's in this varchar field, can you supply an example?

You can try the CAST statement in a SQL Expression to resolve this.

If it fails, supply what's in the field.

The rest is pretty much described. LBass touched on the parameter issue, just make sure that you construct the parameter as a date type too, that way the database does all of the work, and should pass the SQL to the database for optimal performance.

-k

 

[boatie]

Sorry about the delay in getting back:

The data format for the Statitem field is year.weeknumber, eg 2002.01.

The statitem table is updated once a week and is a summary table from a daily transactional table that receives data from a number of sites (in this case sales data).

So basically the data in this table is entered according to site, datestamped, at the start of each week.

For performance reasons it is not feasible for me to go to the source table and query it.

'Vamp mentioned about Casting a date to correlate to the current character in the stattiem table. Pardon my ignorance but would i need to do this for each value in the statitem field or will this automatically regress??