5 Replies Latest reply on Aug 12, 2011 12:31 PM by philmodjunk

Performing Calculations on Summary Fields?

Title

Performing Calculations on Summary Fields?

Post

I'm a beginner, starting from scratch, converting unweildy spreadsheets to FMPro.

I need to compare weekly sales with the same week, one year ago (and preferably 2 or 3 years, but I can extrapolate)

I have fields for week of year in my Daily Sales table, and can get weekly sales reports by location, but am having trouble figuring out how to get my comps.

Any help would be appreciated.

I only have three tables right now: Locations, Sales, and Historical sales.

Honestly, it's all fluid and in the thought process, so if you have any great ideas or suggestions I'd love to hear them.

OR....

Another possible workaround that I can't seem to get right....I've added fields for Prior year date calculations, How do I perform a calculation that will populate the prior year's corresponding sales date with the calc sales for that store?

(PY1 Date is a date calculation field, PY1 Sales is a lookup or some other field I need to pull from a different table, with the Store ID and the Date being the match relationships)

I apologize for being such a noob, but it just makes sense to me that if PY1 Date = Historical Sales::Date and StoreID = Store ID, then give me the results in the field I want. I can't seem to get it to work properly.

TIA

• 1. Re: Performing Calculations on Summary Fields?

If the only difference between Sales and Historical Sales is the date of the transaction, it would be easier to keep all this data inside the same table. You can relate your Sales table to itself in a self-join relationship without needing two separate tables. You use two "occurrences" of the same table instead of separate tables. This avoids having to move this year's data into the history table at year's end and allows for summary reports that combine data from the current year and past years that are easier to set up.

What does one record in sales represent? Total sales for a given store on a given day?

And when your refer to the Historical Sales data from a previous year, do you want to refer to the total sales for that year or that day? Your relationship suggests for the day, but your sentence suggests that you want a yearly total.

Assuming that this is one record per store per day and that you want a yearly total, you can set things up, with a single Sales table like this (though it's much the same if you use two tables.):

define calculation tables in Sales cYear and cPrevYear as: Year (Date) ; Year (Date) - 1 respectively.

In Manage | Database | Sales, click on Sales, then click the button with two green plus signs to make  second occurrence of sales. Double click it and rename it: "PrevYearSales".

Set up this relationship:

Sales::cPrevYear = PrevYearSales::cYear

Then you can show your Previous Year sales total in one of two ways:

1. Define a Summary field in Sales to compute the Total of your Sales amount. Put the Summary field from PrevYearSales on your Sales Layout.
2. Define a calculation field as Sum ( PrevYearSales::SalesAmount ). Put this calculation field on your Sales layout.
• 2. Re: Performing Calculations on Summary Fields?

I was very tired last night, I apologize for the confusion.

I agree that putting historical sales into the sales table is the right way to go.

We do daily and weekly comparisons. One record per store, per day, with comps to the same day last year.

In Excel, we have a weekly subtotal, and that's what we're doing the vlookups on for the weekly comps.

And I guess that's where my confusion lies. I don't want a record for each week, since everything will be in there daily, but I need to summarize the week, compare it to the previous year's week. In excel, we currently have a variance sheet, with all sales and we vlookup the result of this:

=((SUM(this week this year)/SUM(this week last year))-1)

• 3. Re: Performing Calculations on Summary Fields?

Ok, we can do that. we just need to modify the relationship to match by same week instead of same year. There's one catch, however, with our "same week" calculation field. A week can start in December of one year and end in January of the next. Thus figures for this one partial week will not be valid.

cThisWeek : WeekOfYear ( Date ) & " " & Year ( date )
cLastYearWeek : WeekOfYear ( Date ) & " " & Year ( Date ) - 1

(I know of a method for using a date calculation for getting dates for Sunday of each week, Date - DayOfWeek ( Date ) + 1, but can't see how to link that to a week in the previous year. If I could, we would be able to avoid this partial week problem.)

These fields give us this relationship:

Sales::cLastYearWeek = SalesLastYearByweek::cThisWeek AND
Sales::StoreID = SalesLastYearByWeek::StoreID (apologies for forgetting these two fields in the previous example)

And this calculation can use the Summary field, sTotalSales to produce the weekly variance:

getSummary ( cThisWeek ; sTotalSales ) /SalesLastYearByWeek::sTotalSales

This requires that all records for the given store and same week are present in your found set and that you have sorted them by StoreID, then also by cThisWeek to group the records by store, then by week.

It's possible to set up a report where you see one row for each store for each week by using a sub summary part "when sorted by cThisWeek" and then removing the body layout part. This also requires the same sort order. You'd place this calculation field inside the sub summary part along with other fields such as store ID, location, name to produce the desired report.

• 4. Re: Performing Calculations on Summary Fields?

Hopefully I will have time to work on it this afternoon. Thank you so much for your help.

Have you ever tried WeekOfYearFiscal? That's how I'm getting my weeks to start on Sundays. I'm not sure how I'm going to handle it when week 53 is also week 1 as corporate and FMPro don't agree that the first week has to have 4 days in January to be considered week one, but that's another story for another day.

WeekOfYearFiscal(date;startingDay)

StartingDay - any number between 1 and 7, where 1 represents Sunday

Thanks again.
• 5. Re: Performing Calculations on Summary Fields?

I've seen the function before and can see where it would help you synch things closer to what corporate needs. I wonder if some person can share an approach that will work here that also handles the "partial week" problem.

You may have to set up a table with one record for each week to function as a "calendar" for identifying the week each record falls in.

It might have fields such as:

Week, Year, FirstdayDate, cLastdaydate

and a relationship to it could look up a number from Week to establish the correct week number. A script could populate your records in this table for each new year so maintaining the records in this table would not be too difficult once you got everything set up and working right.