SELECT time_hier.member_name AS time, 
       geography_hier.member_name AS geography, 
       sales,
       sales_prior_period, ROUND(sales_prior_period_pct_change,3)
                           AS percent_change_sales
FROM ANALYTIC VIEW (
  USING sales_av 
  HIERARCHIES(time_hier, geography_hier)
  FILTER FACT (
    time_hier TO level_name = 'QUARTER' AND
                              (quarter_name like 'Q1%' 
                               OR quarter_name like 'Q2%'),
    geography_hier TO level_name = 'COUNTRY' 
                   AND country_name in ('Mexico','Canada')
              )
  ADD MEASURES (
    sales_prior_period AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1)),
    sales_prior_period_pct_change AS (LAG_DIFF_PERCENT(sales)
                                      OVER (HIERARCHY time_hier OFFSET 1))
               )
                   )
WHERE time_hier.level_name = 'YEAR'
  AND geography_hier.level_name = 'REGION'
  AND   sales > 30000000
ORDER BY time_hier.hier_order;