Advice please - variable calculations

[neilgalyans]

Can someone give me some advice for a query I need to write..

Based on an date input from a Prompt I have built, I would like MSTR to calculate some different numbers:

1. We have a sales table that is identified with a Year,Period,Week(1-4) and Day of week (1-7) that has a column sls_amount.

I need to compute a week to date amount.. where depending upon the date entered, it will add up the appropriate days..

E.G. If the date input is 10/28/2003 my attributes are set up so Year = 2003 Period = 9 Week = 4 and Day=3.. so I need to return

2003 9 4 1 sls_amount + 2003 9 4 2 sls_amount + 2003 9 4 3 sls_amount -- a week to date total for the 3 days of the week (based on input date having attribute Day = 3).

I need to make this week for all days (i.e. Day=7) and I will want to translate this to a Period to date capability as well. I have a Day of Period attribute for each line as well.

I appreciate any advice you are willing to share!

 

[entaroadun]

Build a week-to-date transformation to use in your metric.

You need a separate day table that contains every date in your sales table.

Build another table that converts each date into the set of dates that compose the week-to-date transformation.

Example:

Date         Week-to-Date Dates
2004 9 4 4     2004 9 4 1
2004 9 4 4     2004 9 4 2
2004 9 4 4     2004 9 4 3
2004 9 4 4     2004 9 4 4

Build a transformation that uses this table. Basically, you are tranforming your one date (2004 9 4 4) into the set of dates that compose the 2004 9 4 4 week-to-date time period.