Same Store Sales

[Former Member]

Using Crystal Reports XI, querying an Access 2003 database with a SQL statement.

My report layout is like this:

Group 1 = Region

Group 2 = Item Type

Group 3 = Item Class

Group 4 = Store

Detail Section - line item detail from invoices (e.g. $$, date)

Group 4 footer

Group 3 footer

Group 2 footer

Group 1 footer

I have a formula for Current Year Net Sales in the Group 4 (store) footer:

if Sum ({@PriYr4wkSale}, {Command.store})+Sum ({@PriYr4wkSpl}, {Command.store}) = 0 then
    0
else
    Sum ({@CurYr4wkSale}, {Command.store})+Sum ({@CurYr4wkSpl}, {Command.store})

This calculates the net sales for the Prior Year. If the previous year sales are zero, then I set the current year net sales to zero, other wise I calculate the current year net sales. I do the same thing for Prior year net sales, but in reverse (if current year is 0, then 0, else calculate prior year).

This gives me correct same store sales data per store. In other words, in the Group 4 footer, I see $0 and $0 for current and prior year if one or both are zero, but if both are non zero then they amount is shown.

My problem is that I want to show the total of the same store sales for goup 3, group 2 and group 1, and CR won't let me summarize that formula.

How can I show true same store sales?

[ido_millet · Accepted solution]

Best long-term approach is to resolve this via a View or SP that gives you total sales per store in each of the last few years.

Alternatively, use a numeric variable and a GF4 formula to accumulate the sums and show the value of that var in a GF3 formula.

Use a GH3 formula to reset the variable to 0.

- Ido

[Former Member · Accepted solution]

Thanks for the replies.

We implemented a new accounting/sales system on April 1, so my long term solution will involve rewriting the report to query the main database directly (rather than the Access go-between I created to store the old system's sales data and the new system's sales data). However, even when I get to this point, I think I will still be stuck for a couple of reasons.

1) We have some stores that replaced old stores. The client closed an old location and opened a new location in the same town; while those two stores would not show sales in one of the two years, we still want to include both of them.

2) For some products and regions we deliver to a depot which then distributes to individual stores. We then get spoil data from the individual stores. So we might have $100,000 is sales to Depot A, and $500 is spoils at Store 1, and $400 from Store 2. It's possible that in one year, Store 1 or Store 2 won't have any spoils in the given 4 week period, but the store is active in both years.

My solution is to manually manage the exclusion list (or force the Sales department to do so :). I will just write it into the SQL query.

[Former Member]

Hi,

I ran into something similar... what I didn't realize is that running total and sum are a little bit different so try both options to see if one works.

Heather

[ido_millet]

Running total will not solve this issue. It is subject to the same limitation (can't total a total).

- Ido