line chart creation 1

[fieldtechuk]

I am fairly new to Crystal and have so far only created very simple reports.

I now need to produce a reasonably simple chart, but can't seem to get my head round how to start let alone do it. Any help would be appreciated.

My table consists of sales records. One record per sale. Each record has a sale date (dd/mm/yyyy). I need to create a line chart which has months Jan to Dec along the x-axis, number of sales on the y-axis, and one plotted line for each years sales. This will therefore show a comparison of number of sales by month for each year.

Many thanks in advance.

 

[lbass]

Create these formulas:

//{@month}:
monthname(month({table.salesdate}))

//{@thisyear}:
if year({table.salesdate}) = year(currentdate) then {table.salesamt}

//{@lastyear}:
if year({table.salesdate}) = year(currentdate)-1 then {table.salesamt}

//{@yearbeforelast}:
if year({table.salesdate}) = year(currentdate)-2 then {table.salesamt}

Add {@month} as the "on change of" field, and then add each of the year formulas as "show value" fields, with sum as the function. In the chart expert, select the on change of field {@month}->order and choose specified order, and then enter the months in order. Otherwise it will sort the months alphabetically.

-LB

 

[fieldtechuk]

Many thanks for the suggestion. It is nearly what I am looking for, but I still have a few problems.
First I don't want the sum of sales just the number, so I changed the function to be a count rather than a sum. The plotted values

are not showing correct though. My data (simplified) is as follows:

       2005  2006
Mar     1     3
April   2     5
May     9    17
June    3     0
July    5     0

The graph is showing for 2005 values of 4,7,26,3 and 5 and for 2006 values of 8,14,52,6 and 10. 2005 is therefore showing the total of both

years, and 2006 is double 2005. Not sure where I have gone wrong.

Also how do I show months for which there is no data (i.e. I really want all 12 months displayed on the x-axis even though I only

have data for March to July at present).

Thanks in advance.

 

[lbass]

Sorry, about not noticing that you wanted a sales count.

First, if you insert a count on a conditional formula, the resulting summary will be the number of times the formula executes, not the count of the specific result of the formula. You should instead use:

if year({table.salesdate}) = year(currentdate) then 1

And then insert a SUM, not count, on the formula. Repeat for the other two formulas, replacing {table.salesamt} with 1. Then follow the earlier steps.

-LB

 

[fieldtechuk]

Thanks - this make much more sense. I have tried this now and it's getting better. The graph for 2005 is now correctly plotting 1,2,9,3,5 but the graph for 2006 is still showing the total of 2005 + 2006 i.e 4,7,26,3 and 5.
So nearly there but not quite.

 

[lbass]

Please copy the formulas you are using for each year into the thread.

-LB

 

[fieldtechuk]

On change of
@month -S

show value(s)
Sum of @lastyear
Sum of @thisyear

where @month = monthname(month({request.created_dt}))
@lastyear = if year({request.created_dt})=year(currentdate)-1 then 1
@thisyear = if year({request.created_dt})=year(currentdate) then 1

 

[lbass]

I see nothing wrong with this. Where are you placing the chart? It should be in the report footer. I tested this approach and it worked here.

I forgot that you wanted to show results for all months even if there is no data though. I think you would have to have or create a table that contains all dates in the two year period. You would then use a left join FROM the alldates table to your Request table. Use {alldates.date} for your month formula.

-LB

 

[fieldtechuk]

The chart was in the report header. I've moved it to the report footer and no change. Can't understand what's wrong.
I've displayed the values of the two functions on the report and for last year get 1's and o's for lastyear, thisyear and for this year I get 0's and 1's. ??

 

[lbass]

The location in the report header should be fine. I only meant you would get different results if you placed the chart in a group section.

I think your chart type selection is the issue. Try going back to the first tab of the chart expert, and make sure you have not selected the stacked line chart option--that would add the values from the years together. Just select the first line chart option.

-LB