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 strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

4 Week Rolling Date Not working in Graph 1

Status
Not open for further replies.

GC2000

Programmer
Sep 19, 2008
27
CA
So I am creating a report that is based on a Fiscal year from April 1st to March 31st. In this report I have several calculations including Reported Week, MTD, Rolling 4 Weeks and Fiscal YTD. Everything works fine except when I try to create a graph for the rolling 4 weeks. Its a bar graph and it is added a blank bar before showing the dates. Here is the formula I used for the rolling 4 weeks:

if {table.date} in
cdate(DateAdd("ww", -4, cdate(dateadd("d",-(dayofweek(currentdate)-1),currentdate)))) to
cdate(dateadd("d",-dayofweek(currentdate),currentdate)) then
{table.date}

So I decided to use a subreport and while this does pull the results correctly since I force the above formula in the select expert it seems odd that I have to the subreport query against the report data when I already have it. I checked the formula as a group and I believe the issue is that it shows the last 4 weeks but the data before that is represented as a null value. Any ideas?
 
First create a formula {@null} by opening and saving a new formula without entering anything. Then change your formula to:

if {table.date} in
cdate(DateAdd("ww", -4, cdate(dateadd("d",-(dayofweek(currentdate)-1),currentdate)))) to
cdate(dateadd("d",-dayofweek(currentdate),currentdate)) then
{table.date} else
date({@null})

Change your summary field to:

if {table.date} in
cdate(DateAdd("ww", -4, cdate(dateadd("d",-(dayofweek(currentdate)-1),currentdate)))) to
cdate(dateadd("d",-dayofweek(currentdate),currentdate)) then
{table.amount} else
tonumber({@null})

-LB
 
I made the above changes and the blank bar is still there. I guess the only additional piece of information I can add is that the chart is a combined bar and line chart with 2 y axis although I don't see why that would make any difference since the chart is grouping on the 4 week date formula. Also I am using Crystal Reports 2008.
 
If you have more than one summary or on change of field, you must add the conditional clause to each of them.

-LB
 
So I put the conditions on both summaries and on the change of field but it still provides the space. To ensure the issues wasn't with one of the summaries I removed one and the additional space is still there.
 
There might be a space, but there shouldn't be a bar anyway. I would stick with the subreport then. I did test this though, and it worked when I used only one on change of field and one summary.

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top