Sums and difference CR 9

[Brian555]

I have a database with two tables. The structure of each is identical (see below). Table 1 (2004 sales) contains sales data from 2004 and table 2 (2005 sales) contains sales data for 2005.

Table structure (Simplified).

Month Area Shop No of sales
Jan-04 Area 1 Shop 101 6
Jan-04 Area 1 Shop 102 8
Jan-04 Area 2 Shop 201 5
Jan-04 Area 2 Shop 202 10
Feb-04 Area 1 Shop 101 7
Feb-04 Area 1 Shop 102 5
Feb-04 Area 2 Shop 201 6
Feb-04 Area 2 Shop 202 8

What I am trying to do seems simple yet I am struggling.

I want to sum the sales, by area and year, for example Jan-04 and Jan-05 and then show the difference.

Ideal report structure:

Jan-04 Jan-05 Difference
Area 1 14 13 -1
Area 2 15 20 5

I have created a cross-tab to show the first 3 columns, i.e Area, Jan-04 and Jan-05 but I cannot create the difference column.

Can anybody help!

 

[lbass]

Try using a union all statement in order to combine the two tables. Use "Add command" as your datasource for the report, and then set up the SQL query something like:

Select
Table1.`Month`,Table1.`Area`,Table1.`Shop`,Table1.`Sales`
From
`Table1` Table1
Union All
Select
Table2.`Month`,Table2.`Area`,Table2.`Shop`,Table2.`Sales`
From
`Table2` Table2
Order By
2,1

This will "merge" like fields. Then you should be able to do a manual crosstab, inserting a group on {Table1.Area} and then writing detail formulas like:

//{@2004}:
if {table.month} in ["Jan-04","Feb-04" //etc.] then {table.sales}

Repeat for 2005. Then insert summaries on each formula and suppress the detail section. For the difference you can use:

sum({@2005},{table.area})-sum({@2004},{table.area})

-LB

 

[Brian555]

Sincere thanks for your help

Problem sorted.

regards

Brian (snoweyboy)