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

    Trying to calculate sales by store, weekly average

    John Bencina

      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]

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

       

      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