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

 

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.

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?

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.

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 ( mailto:%7b@2009 - {@2009 }, mailto:%7b@month%7d%29=0 - {@month})=0 then 100 else
(Sum ( mailto:%7b@2010 - {@2010 }, mailto:%7b@month%7d%29-Sum - {@month})-Sum ( mailto:%7b@2009 - {@2009 }, mailto:%7b@month - {@month })) / Sum ( mailto:%7b@2009 - {@2009 }, mailto:%7b@month - {@month })

 

Create a bar chart

on chnage of @month

Show value @monthly_difference with 'don't summarize' checked as true

 

 

Thanks and regards

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:

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:

 

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 ( mailto:%7b@2009 - {@2009 }, {SalespersonHistory.FiscalPeriod})=0 then 100 else
(Sum ( mailto:%7b@2010 - {@2010 }, {SalespersonHistory.FiscalPeriod})-Sum ( mailto:%7b@2009 - {@2009 }, {SalespersonHistory.FiscalPeriod})) / Sum ( mailto:%7b@2009 - {@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?

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?

 

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?

drop group one (FY)

you need your years to stay togther under each salesperson and month

if you split your years at group level 1 you cannot compare as you desire