DMX cross prediction query

Question

Dear All,

Below query is applying the total sales model to each store in dmx query but I am only able to do this for one store at a time . But I want to receive the predictions for 100 stores like 3000,3001,3002, ...,3022,....Where can I put this? I can only filter one store which is not practical.

Thank you

SELECT FLATTENED
  ([AllStores].[SUBE KODU]) as [Model Used],
  ('3022') as [AllStores],
  (PredictTimeSeries([AllStores].[Ty Sales Unit],3, REPLACE_MODEL_CASES)) as [Predicted Quantity]
FROM
  [AllStores]
PREDICTION JOIN
  OPENQUERY([ForecastSales],
    'SELECT
       [WEEK]
      ,[STORE]
      ,[Ty Sales Unit]
   FROM
          dbo.[SalesByStore]
WHERE        [STORE] =3022
    ' ) AS t

ON
  [AllStores].[WEEK]            =    t.[WEEK] AND
  [AllStores].[Ty Sales Unit] =    t.[Ty Sales Unit]

Answer 1

Dear All,

Below query is applying the total sales model to each store in dmx query but I am only able to do this for one store at a time . But I want to receive the predictions for 100 stores like 3000,3001,3002, ...,3022,....Where can I put this? I can only filter one store which is not practical.

Thank you

SELECT FLATTENED
  ([AllStores].[SUBE KODU]) as [Model Used],
  ('3022') as [AllStores],
  (PredictTimeSeries([AllStores].[Ty Sales Unit],3, REPLACE_MODEL_CASES)) as [Predicted Quantity]
FROM
  [AllStores]
PREDICTION JOIN
  OPENQUERY([ForecastSales],
    'SELECT
       [WEEK]
      ,[STORE]
      ,[Ty Sales Unit]
   FROM
          dbo.[SalesByStore]
WHERE        [STORE] =3022
    ' ) AS t

ON
  [AllStores].[WEEK]            =    t.[WEEK] AND
  [AllStores].[Ty Sales Unit] =    t.[Ty Sales Unit]

Answer 2

Hi m2masari,

I suggest you can try to use OR after WHERE in the query, please refer to the following article:
http://msdn.microsoft.com/en-us/library/ms132167.aspx

Thanks,
Eileen