Accumulated Sales metric help

[bittmapp]

Hi,

I have a report that has a prompt for to pick month.

The report has item as an attribute and sales as the metric.

I would like to create a new metric that shows the accummulated sales for the item from the item's start date. The item's start date could be last month or could possibly be last year or anydate for that matter. Any ideas how to do this and show it in my report?

Thanks,

bitt

 

[entaroadun]

The easiest way is to create a custom filter and embed it in the filter. Unfortunately, this way, you won't be able to use an attribute element prompt (drop-down list); you'll be forced to use a value prompt (free-form text).

If you need the drop-down, you'll have to create a transformation, and use that transformation in the metric.

Both options are in-depth and complicated, so I won't continue until you pick an option. I don't feel like typing much today.

 

[bittmapp]

I wish to use the dropdown for the prompt. How would I set up the transformation table?

Thanks in advance.

bitt

 

[entaroadun]

I spoke too soon. You'll need to create a completely new fact table, not a transformation. The principle is the same.

Create a view using inequality joins to multiply the rows in your Sales table:

SELECT Time.TimeID AS TimeID
     , Sales.TimeID AS SalesTimeID
     , Sales.SalesFact AS SalesFact
     , [necessary key attribute 1] etc.
FROM   Sales
  JOIN Product
    ON Sales.ProductID = Product.ProductID
   AND Sales.TimeID >= Product.StartTimeID
  JOIN Time
    ON Sales.TimeID <= Time.TimeID

Bring the view into MSTR. Connect your Time attribute to TimeID on the view. Create a new fact named "Accumulated Sales" and point it to SalesFact on this view. Remember to connect all of the attributes that are on the regular Sales table to this new view. (This is important!)

Create a new metric named "Accumulated Sales" using the "Accumulated Sales" fact. You don't need any special settings.

The approach is simple. You are adding an additional TimeID column to your Sales table. The joins are set up to repeat the Sales rows for each DateID in the Time table.

Ex: You have a product that began selling in 3/1/2000, and you have Sales fact data from 3/3/2000 to 12/31/2004. You are reporting on 6/30/2003, and you want all Sales from the Product Start Date to 6/30/2003. The view is set up such that if you filter on TimeID=6/30/2003, you will get every row from the Sales that where the Sales.TimeID is on or before 6/30/2003, and the Sales.TimeID is on or after the Product Start Date.

Unfortunately, MSTR cannot do inequality joins. This approach circumvents it. Because it is a completely different fact table, MSTR will pull "Accumulated Sales" from it in a completely different pass than all of the other facts. This isolates it from the other metrics.

Performance depends on how your RDBMS resolves the inequality joins. You may use the following simplified SQL for performance:

SELECT Time.TimeID AS TimeID
     , Sales.TimeID AS SalesTimeID
     , Sales.SalesFact AS SalesFact
     , [necessary key attribute 1] etc.
FROM   Sales
  JOIN Time
    ON Sales.TimeID <= Time.TimeID

The Product table has been removed. This SQL assumes that there will be no entries in the Sales table for dates before a Product Start Date, so it's not necessary to set the Sales.TimeID >= Product.TimeID criteria. If this is a safe assumption, use this SQL instead.

 

[bittmapp]

Thanks entaroadun, you have been most patient

I give this logic a try, I will be sure to let you know how I find this.


bitt