Moving-Average that excludes specific records.

[qberta01]

Hello,

Software: Cognos BI – Report Studio, Relational Database, v10.0

Object: Moving-Average of sales for 2 weeks back, only considering products that have at least 4 weeks on sale.

Data Sample:
Product Category Product Type Weeks On Sale Sales
Office Supplies Pad of paper 1 10
Office Supplies Pad of paper 2 4
Office Supplies Pad of paper 3 5
Office Supplies Pad of paper 4 2
Office Supplies Pad of paper 5 1
Office Supplies Pens 1 1
Office Supplies Pens 2 2
Office Supplies Pens 3 2
Office Supplies Pens 4 3
Office Supplies Pencils 1 2
Office Supplies Pencils 2 1
Office Supplies Pencils 3 1
Office Supplies Erasers 1 1
Office Supplies Erasers 2 2

Example: Office Supplies – Erasers – Weeks on Sale 2, should average the following:

Pens – Weeks on Sale 2 – Sales 2
Pad of Paper – Weeks on Sale 2 – Sales 4

Average = 6/2 = 3

Pencils not considered because it only has 3 weeks on Sale.

This is just a simple example and is not real data. Don’t mean to “dumb” down the example, but it seems confusing to me. I cannot get Cognos to work with me on it.

This is what I have so far:
moving-average ([Sales],2 for [Weeks On Sale])

Is there an obvious/easy way for it to exclude from averages where product type does not have at least 4 weeks on Sale?

Thx in advance,

Q.