Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Charts that give the growth between years Post Reply Post New Topic
Page  of 2 Next >>
Author Message
fgoitre
Newbie
Newbie
Avatar

Joined: 04 Sep 2010
Location: Italy
Online Status: Offline
Posts: 17
Quote fgoitre Replybullet Topic: Charts that give the growth between years
    Posted: 04 Sep 2010 at 5:33am
Hello
I am new user of crystal reports and I will like to make the following charts.
The first one should compare the qty of units sold on 2009 & 2010. The information shoul be divided by month.
The second chart should show the growth between 2009 & 2010 express on %.
 
For the first chart, I have read on this forum how to do it.
I have created a formula "month":
DatePart ('m',{'Truck_Bookings_'.Truck Bookings Date} )
Then I have created 2 running totals, 1 for 2009 and 1 for 2010
On the 2009 running total, i have use:
Year ({'Truck_Bookings_'.Truck Bookings Date})=2009 
 
The same for 2010. Reseting this total "on changing group"
 
For the 2nd chart, I have tried to make a running total with the following formula but I receive an error.
 
Thanks in advance
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 08 Sep 2010 at 10:19am
can you explain a little further please as to what the error is you are getting and where.
IP IP Logged
fgoitre
Newbie
Newbie
Avatar

Joined: 04 Sep 2010
Location: Italy
Online Status: Offline
Posts: 17
Quote fgoitre Replybullet Posted: 08 Sep 2010 at 11:09am
I want to obtaion the % value of growth between 2010 & 2009 (divided by quarters).
I have created 2 running totals, 1 for 2009 & 1 for 2010.
 
When I want to create the chart, I am ordering the information with "order date" (divided by quarters) and on the data field I select the 2010 running total. If I select a running total (instead of a normal field of the database), I have no options (like sum, correlation, etc).
Maybe running totals is not the correct way for obtaing this result but if I use the formulas I really don't know how to compare 2010 results with 2009 results.
I hope now is more clear.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 08 Sep 2010 at 11:25am
There are a number of ways to get what you want...
Running totals can often be used in charts as the summay item but not as grouping.
I need to visualize what you want your chart to look like.
Can you explain it a little more?...
Side by Side quarter totals?
Stacked quarter totals?
side by side year stacked with quarters?
IP IP Logged
fgoitre
Newbie
Newbie
Avatar

Joined: 04 Sep 2010
Location: Italy
Online Status: Offline
Posts: 17
Quote fgoitre Replybullet Posted: 08 Sep 2010 at 12:06pm
Excuse me but on my last e-mail I wrote quarters instead of month.
For the 1st chart (month compared side by side) the running totals works fine.
For the growth rate chart, I will need only one bar for each month. These bars has to represent the following:
for ex: ("Jan 2010" - "Jan 2009")/"Jan 2009"
 
This chart will have only 12 columns, one for each month.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 09 Sep 2010 at 4:29am

you will need to create 4 formula's

1. 2009 data called '2009'
if year({'Truck_Bookings_'.Truck Bookings Date})=2009 then table.amount else 0
2. 2010 data called '2010'
if year({'Truck_Bookings_'.Truck Bookings Date})=2010 then table.amount else 0
3. MOnth part for grouping called 'Month'
totext(month({'Truck_Bookings_'.Truck Bookings Date}),'00') + '-' + monthname(month({'Truck_Bookings_'.Truck Bookings Date}),TRUE)
Group on this @month formula field
4. difference formula as 'monthly_difference'
if Sum ({@2009}, {@month})=0 then 100 else
(Sum ({@2010}, {@month})-Sum ({@2009}, {@month})) / Sum ({@2009}, {@month})
 
Create a bar chart
on chnage of @month
Show value @monthly_difference with 'don't summarize' checked as true
 
IP IP Logged
fgoitre
Newbie
Newbie
Avatar

Joined: 04 Sep 2010
Location: Italy
Online Status: Offline
Posts: 17
Quote fgoitre Replybullet Posted: 10 Sep 2010 at 12:36am
Great!!!
 
Thanks and regards
IP IP Logged
atminnie
Newbie
Newbie


Joined: 19 Jul 2010
Online Status: Offline
Posts: 9
Quote atminnie Replybullet Posted: 10 Sep 2010 at 3:39am
Hello, I hope I'm not threadjacking, but it looks like the original poster has found resolution.
 
I've been watching this thread because I am trying to make a similar report.  My report shows sales ({SalespersonHistory.CommissionDollarsSold}) by salesperson by fiscal period (i.e. month).
I would like to add/create a field with "% change" for each month from last year that same month to this year. 
 
I believe your formulas and descriptions could work but I'm not sure how the example fields here line up with my fields since some are different data types.
 
So far I'm working with the fields:
  • {SalespersonHistory.FiscalPeriod} (a string field with the month as a number, 1-12=Jan-Dec)
  • {SalespersonHistory.FiscalYear} (another string field).  
  • {SalespersonHistory.CommissionDollarsSold} (number field)
So far I am not working with a date field for each record like {'Truck_Bookings_'.Truck Bookings Date} in the example.
 
Would your instructions work in this case? If so, how do I need to alter it?
 
I tried making the 2009 formula as follows:
if ({SalespersonHistory.FiscalYear})="2009" then {SalespersonHistory.CommissionDollarsSold} else 0
 
I've made "@2010" similar to @2009 but with "2010" of course, and the "@monthly_difference" formula (which I call "@%_change") as follows:
if Sum ({@2009}, {SalespersonHistory.FiscalPeriod})=0 then 100 else
(Sum ({@2010}, {SalespersonHistory.FiscalPeriod})-Sum ({@2009}, {SalespersonHistory.FiscalPeriod})) / Sum ({@2009}, {SalespersonHistory.FiscalPeriod})
 
I'm not using the @month formula because {SalespersonHistory.FiscalPeriod} already isolates the month. I don't get any errors from the formulas but the %_change fields in the report calculate only as either 100.00, or -1.00. 
 
What am I missing?


Edited by atminnie - 10 Sep 2010 at 4:12am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 10 Sep 2010 at 4:47am
I think you lined things up fairly well.
What is your group structure?
I would think you need group1=customer and group 2 = month
how do you need the chart to look?
IP IP Logged
atminnie
Newbie
Newbie


Joined: 19 Jul 2010
Online Status: Offline
Posts: 9
Quote atminnie Replybullet Posted: 10 Sep 2010 at 5:25am
I don't need a chart, I just need the calculation of % Change for each month for each salesperson.
 
My groups are currently laid out:
 
Group 1. FiscalYear
  Group 2. SalespersonName
    Group 3. FiscalPeriod
 
This structure is easily subject to change.
Am I right in supposing the string-type {...FiscalPeriod} field stands in for your example's @month formula?


Edited by atminnie - 10 Sep 2010 at 6:30am
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.