MDX query date range

Question

I am working pentaho dashboard trying to generate report to get transaction count per day between two date. i am beginner to mdx query below query shows only two date transaction count what i want is range between. i know it how to do it on simple query

SQL Working Query

SELECT
    DATE(modified) AS trndate,
    COUNT(id)
FROM
    log.transaction
   WHERE  DATE(modified) BETWEEN DATE(${from_date}) AND DATE(${to_date})

GROUP BY
     trndate

MDX Query which shows only two date transaction instead i want range between two date

WITH
SET [~ROWS] AS
    {[created].[2014-10-01 12:01:53.507787], [created].[2014-10-01 20:34:14.410064]}
SELECT
NON EMPTY {[Measures].[id]} ON COLUMNS,
NON EMPTY [~ROWS] ON ROWS
FROM [transaction]

whytheq whytheq · Accepted Answer · 2018-01-09T19:15:06

Usually you can use a colon to specify a range

WITH
SET [~ROWS] AS
    {[created].[2014-10-01 12:01:53.507787]:[created].[2014-10-01 20:34:14.410064]}
SELECT
NON EMPTY {[Measures].[id]} ON COLUMNS,
NON EMPTY [~ROWS] ON ROWS
FROM [transaction]

MDX query date range

1 Answers