Trouble making microstradegy perform same as freeform sql

[timmahoe]

The main part of the code that I am trying to re-create in mstr is not working out so well. I have pasted the code below, and basically it's taking one store (locid) grabbing the first 4 week numbers from its open date and then taking those 4 week numbers and applying the same dates or forecast_week_nbrs to a different stores forecast week amounts which coincide with the forecast_week_nbrs as you will see in the case statement. There is also a sku selection in there as well which is there for runtime quickness. I already created the first part of the report, which is the sub-select in the code below, where I am getting stuck is when I try to take that info generated and apply it to the rest of the code. I guess if anyone has any suggestions or examples that would be greatly appreciated. I also know the terms so if you say create a filter with metrics or attributes etc. I know what you are talking about. Thanks! The code is as follows in freeform:


select a.locid mstore, x.locid newstore, a.itemid skuu, forecast_week_nbr fwn,
( case
when x.forecast_week_nbr = 01
then a.week01_forecast_amt
when x.forecast_week_nbr = 02
then a.week02_forecast_amt
when x.forecast_week_nbr = 03
then a.week03_forecast_amt
when x.forecast_week_nbr = 04
then a.week04_forecast_amt
when x.forecast_week_nbr = 05
then a.week05_forecast_amt
when x.forecast_week_nbr = 06
then a.week06_forecast_amt
when x.forecast_week_nbr = 07
then a.week07_forecast_amt
when x.forecast_week_nbr = 08
then a.week08_forecast_amt
when x.forecast_week_nbr = 09
then a.week09_forecast_amt
when x.forecast_week_nbr = 10
then a.week10_forecast_amt
when x.forecast_week_nbr = 11
then a.week11_forecast_amt
when x.forecast_week_nbr = 12
then a.week12_forecast_amt
when x.forecast_week_nbr = 13
then a.week13_forecast_amt
when x.forecast_week_nbr = 14
then a.week14_forecast_amt
when x.forecast_week_nbr = 15
then a.week15_forecast_amt
when x.forecast_week_nbr = 16
then a.week16_forecast_amt
when x.forecast_week_nbr = 17
then a.week17_forecast_amt
else 0
end) forecast_amt

from
prodmsivws.inventory_forecastv a,
(select
a.locid,
b.forecast_week_nbr
from Prodmsivws.LOCATION_Dimensionv a,
Prodmsivws.Inventory_Forecast_Week_Nbrv b
where
b.the_date between a.soft_open_date and a.soft_open_date + 21
and a.locid = 3232) x
where a.locid = 3011
and a.itemid = 1021017
and a.Forecast_Type_Cd in (1,3)
group by 1,2,3,4,5