1 Reply Latest reply on May 28, 2013 8:18 PM by John Bencina

# Trying to calculate sales by store, weekly average

My workbook has a calculated field called [Store Count] which determines unique store counts as

COUNTD([SL_STORENUM])

I have another calculated field called [Sales By Store] which determines sales per store as

SUM([DST_UNITS])/[Store Count]

I'm trying to create a report which has different regions as rows (North, East, etc) and for columns I want a few measures:

• Last Week Sales Per Store
• 4WK Avg Sales Per Store
• 16WK Avg Sales Per Store

I have the below calculated field as a standard 4WK average calculation. This works for a total corporate level 4WK average, but I cannot figure out how to make this accept [Sales By Store]

I tried this, but I get a blank column

(IF Attr([DST_DATE])>=DATEADD('week',-4,[Trailing As Of]) AND Attr([DST_DATE])<=DATEADD('week',-1,[Trailing As Of]) THEN [Sales Per Store] END)/4

• ###### 1. Re: Trying to calculate sales by store, weekly average

Sorry. I've been looking at this problem the completely wrong way and just solved it!

SUM(IF [DST_DATE]>=DATEADD('week',-4,[Trailing As Of]) AND [DST_DATE]<=DATEADD('week',-1,[Trailing As Of]) THEN [DST_UNITS] END)/[Store Count]/4

All that was needed was to put the store count outside the Sum aggregation