Pentaho "Current Week" filter shows previous week

analysis.md

Selecting "Current Week" as a filter actually shows the previous week. For example, today is 2015-01-27 which is in 2015-W05 but Pentaho shows 2015-W04.

"Previous Week" and "Next Week" are similarly off-by-one (showing 2015-W03 and 2015-W05 respectively)

The Today, Yesterday and Tomorrow filters work fine.

create.sql

drop table if exists fact_test;

create table fact_test (
  date_key int unsigned not null,
  key idx_date (date)
);

drop table if exists dim_date;

create table dim_date (
  date_key int unsigned not null primary key,
  date date not null,
  year varchar(4) not null,
  month varchar(7) not null,
  week varchar(8) not null,
  quarter varchar(7) not null,
  key idx_date (date),
  key idx_year (year),
  key idx_month (month),
  key idx_week (week)
);

insert into dim_date
  (date_key,  date,         year,   quarter,   month,     week     ) values
  (20150105, '2015-01-05', '2015', '2015-Q1', '2015-01', '2015-W02'),
  (20150106, '2015-01-06', '2015', '2015-Q1', '2015-01', '2015-W02'),
  (20150107, '2015-01-07', '2015', '2015-Q1', '2015-01', '2015-W02'),
  (20150108, '2015-01-08', '2015', '2015-Q1', '2015-01', '2015-W02'),
  (20150109, '2015-01-09', '2015', '2015-Q1', '2015-01', '2015-W02'),
  (20150110, '2015-01-10', '2015', '2015-Q1', '2015-01', '2015-W02'),
  (20150111, '2015-01-11', '2015', '2015-Q1', '2015-01', '2015-W02'),
  (20150112, '2015-01-12', '2015', '2015-Q1', '2015-01', '2015-W03'),
  (20150113, '2015-01-13', '2015', '2015-Q1', '2015-01', '2015-W03'),
  (20150114, '2015-01-14', '2015', '2015-Q1', '2015-01', '2015-W03'),
  (20150115, '2015-01-15', '2015', '2015-Q1', '2015-01', '2015-W03'),
  (20150116, '2015-01-16', '2015', '2015-Q1', '2015-01', '2015-W03'),
  (20150117, '2015-01-17', '2015', '2015-Q1', '2015-01', '2015-W03'),
  (20150118, '2015-01-18', '2015', '2015-Q1', '2015-01', '2015-W03'),
  (20150119, '2015-01-19', '2015', '2015-Q1', '2015-01', '2015-W04'),
  (20150120, '2015-01-20', '2015', '2015-Q1', '2015-01', '2015-W04'),
  (20150121, '2015-01-21', '2015', '2015-Q1', '2015-01', '2015-W04'),
  (20150122, '2015-01-22', '2015', '2015-Q1', '2015-01', '2015-W04'),
  (20150123, '2015-01-23', '2015', '2015-Q1', '2015-01', '2015-W04'),
  (20150124, '2015-01-24', '2015', '2015-Q1', '2015-01', '2015-W04'),
  (20150125, '2015-01-25', '2015', '2015-Q1', '2015-01', '2015-W04'),
  (20150126, '2015-01-26', '2015', '2015-Q1', '2015-01', '2015-W05'),
  (20150127, '2015-01-27', '2015', '2015-Q1', '2015-01', '2015-W05'),
  (20150128, '2015-01-28', '2015', '2015-Q1', '2015-01', '2015-W05'),
  (20150129, '2015-01-29', '2015', '2015-Q1', '2015-01', '2015-W05'),
  (20150130, '2015-01-30', '2015', '2015-Q1', '2015-01', '2015-W05'),
  (20150131, '2015-01-31', '2015', '2015-Q1', '2015-01', '2015-W05'),
  (20150201, '2015-02-01', '2015', '2015-Q1', '2015-02', '2015-W05'),
  (20150202, '2015-02-02', '2015', '2015-Q1', '2015-02', '2015-W06'),
  (20150203, '2015-02-03', '2015', '2015-Q1', '2015-02', '2015-W06'),
  (20150204, '2015-02-04', '2015', '2015-Q1', '2015-02', '2015-W06'),
  (20150205, '2015-02-05', '2015', '2015-Q1', '2015-02', '2015-W06'),
  (20150206, '2015-02-06', '2015', '2015-Q1', '2015-02', '2015-W06'),
  (20150207, '2015-02-07', '2015', '2015-Q1', '2015-02', '2015-W06'),
  (20150208, '2015-02-08', '2015', '2015-Q1', '2015-02', '2015-W06');

schema.xml

<?xml version="1.0"?>
<Schema name="Date Test">
  <Dimension name="Date" type="TimeDimension">
    <Hierarchy name="Date" primaryKey="date_key" hasAll="true">
      <Table name="dim_date" />
      <Level name="Date" column="date" type="String" uniqueMembers="true" levelType="TimeDays">
        <Annotations>
          <Annotation name="AnalyzerDateFormat">[yyyy-MM-dd]</Annotation>
        </Annotations>
      </Level>
    </Hierarchy>
    <Hierarchy name="Month" primaryKey="date_key" hasAll="true">
      <Table name="dim_date" />
      <Level name="Year" column="year" type="Numeric" uniqueMembers="true" levelType="TimeYears">
        <Annotations>
          <Annotation name="AnalyzerDateFormat">[yyyy]</Annotation>
        </Annotations>
      </Level>
      <Level name="Quarter" column="quarter" type="String" uniqueMembers="false" levelType="TimeQuarters">
        <Annotations>
          <Annotation name="AnalyzerDateFormat"><![CDATA[[yyyy].[yyyy-'Q'q]]]></Annotation>
        </Annotations>
      </Level>
      <Level name="Month" column="month" type="String" uniqueMembers="false" levelType="TimeMonths">
        <Annotations>
          <Annotation name="AnalyzerDateFormat"><![CDATA[[yyyy].[yyyy-'Q'q].[yyyy-MM]]]></Annotation>
        </Annotations>
      </Level>
    </Hierarchy>
    <Hierarchy name="Week" primaryKey="date_key" hasAll="true">
      <Table name="dim_date" />
      <Level name="Year" column="year" type="Numeric" uniqueMembers="true" levelType="TimeYears">
        <Annotations>
          <Annotation name="AnalyzerDateFormat">[YYYY]</Annotation>
        </Annotations>
      </Level>
      <Level name="Week" column="week" type="String" uniqueMembers="true" levelType="TimeWeeks">
        <Annotations>
          <Annotation name="AnalyzerDateFormat"><![CDATA[[YYYY].[YYYY-'W'ww]]]></Annotation>
        </Annotations>
      </Level>
    </Hierarchy>
  </Dimension>
  <Cube name="Test">
    <Table name="fact_test" />
    <DimensionUsage name="Date" source="Date" foreignKey="date_key" />
  </Cube>
</Schema>

Excelente explicación, gracias por compartir. Tengo una duda al respecto de la jerarquía Semestre pues no encuentro su notación. Tienes algún ejemplo que puedas compartir o donde encontrar la notación para el semestre.

Un saludo.