Pivot Table - Compare Current Year Sales To Previous Year Sales

Pivot Table - Compare Current Year Sales To Previous Year Sales

Post by Qm9iQUpvbm » Wed, 19 Apr 2006 15:03:01


Hello:
I'm hoping a Pivot Table can help is compare Sales by month from the current
year vs the previous year.

Level 1 - Corporate Sales
Corporate Jan Feb Mar April
2006 500,000 600,000 550,000 700,000
2005 600,000 525,000 615,000 650,000
Difference -100.000 75,000 -65,000 50,000

Drill Down to next lower level to see the following comparison by store:
Level 2 - Store Sales
Store 1 Jan Feb Mar April
2006 300,000 325,000 315,000 400,000
2005 350,000 275,000 415,000 375,000
Difference -50,000 50,000 -100,000 25,000

Store 2 Jan Feb Mar April
2006 200,000 275,000 235,000 300,000
2005 250,000 250,000 200,000 275,000
Difference -50,000 25,000 35,000 25,000

Drill to next level for Store 1's breakdown of Grocery vs Non-Grocery Sales
Difference -50,000 50,000 -100,000 25,000
Grocery Jan Feb Mar April
2006 200,000 210,000 200,000 250,000
2005 210,000 190,000 225,000 225,000
Difference -10,000 20,000 -25,000 25,000

Non-GroceryJan Feb Mar April
2006 100,000 115,000 115,000 150,000
2005 140,000 85,000 190,000 150,000
Difference -40,000 30,000 -75,000 0

Drill down to next Level to see the breakdown of Store 1's Grocery Sales by
Meat & Non-Meat, etc.

I would always want to keep the totals for each group together. Can this be
done? Are there any examples of how to do something like this?

Thanks in advance for any suggestions!!!

BobAJones
 
 
 

1. Comparing quarterly sales data to previous quarter and previous year.

2. Compare given period in current year / previous year

Hi
I want to write a function that can return a sum for a given date
range. The same function should be able to return the sum for the same
period year before.

Let me give an example:
The Table LedgerTrans consist among other of the follwing fields
AccountNum (Varchar)
Transdate
AmountMST (Real)

The sample data could be
1111, 01-01-2005, 100 USD
1111, 18-01-2005, 125 USD
1111, 15-03-2005, 50 USD
1111,27-06-2005, 500 USD
1111,02-01-2006, 250 USD
1111,23-02-2006,12 USD

If the current day is 16. march 2006 I would like to have a function
which called twice could retrive the values.
Previus period (for TransDate >= 01-01-2005 AND TransDate <=
16-03-2005) = 275 USD
Current period (for TransDate >= 01-01-2006 AND TransDate <=
16-03-2006) = 262 USD
The function should be called with the AccountNum and current date
(GetDate() ?) and f.ex. 0 or 1 for this year / previous year.
How can I create a function that dynamically can do this ?

I have tried f.ex. calling the function with
@ThisYear as GetDate()
SET @DateStart = datepart(d,0) + '-' + datepart(m,0) +
'-'+datepart(y,@ThisYear)
But the value for @dateStart is something like 12-07-1905 so this
don't work.

I Would appreciate any help on this.
BR / Jan

3. Comparing current year and previous year data

4. compare last year and this years sales

5. total sales compared with year to date sales

6. Best Super property sale site for you......sale...sale...sale...sale...sale...sale...

7. Compare this year vs. previous year data in one graph (date vs. quantity)

8. Need help with query for previous year spend vs. current year

9. Need help with query for previous year spend vs. current year spen

10. Query October 1 previous year to September 30 current year

11. Formula for showing Previous Year - Current Year

12. Query for current year dates plus December of previous year??

13. Comparing current year and prior year data in one report

14. Analyst predicts Mac sales for March quarter will be up 42 percent year-over-year

15. Mac US sales up 21% year-over-year in October, November