Labels

Apache Hadoop (3) ASP.NET (2) AWS S3 (2) Batch Script (3) BigQuery (21) BlobStorage (1) C# (3) Cloudera (1) Command (2) Data Model (3) Data Science (1) Django (1) Docker (1) ETL (7) Google Cloud (5) GPG (2) Hadoop (2) Hive (3) Luigi (1) MDX (21) Mongo (3) MYSQL (3) Pandas (1) Pentaho Data Integration (5) PentahoAdmin (13) Polybase (1) Postgres (1) PPS 2007 (2) Python (13) R Program (1) Redshift (3) SQL 2016 (2) SQL Error Fix (18) SQL Performance (1) SQL2012 (7) SQOOP (1) SSAS (20) SSH (1) SSIS (42) SSRS (17) T-SQL (75) Talend (3) Vagrant (1) Virtual Machine (2) WinSCP (1)

Monday, March 21, 2016

How to filter MDX result Set by Current Year?



Below MDX query is to perform dynamic monthly comparison of a measure with current and previous year months,  to achieve this, a current year parameter is applied as like below

WITH
MEMBER [Measures].[ActiveLearnerPercentPreviousYear] as ([Measures].[Active Learner %],
PARALLELPERIOD ([MonthlyPeriod].[Calendar Period].[Month],12,[MonthlyPeriod].[Calendar Period].CurrentMember)),  FORMAT_STRING ='Percent'
MEMBER [Measures].[ActiveLearnerPercentCurrentYear]   as([Measures].[Active Learner %])
SELECT  {[Measures].[ActiveLearnerPercentCurrentYear] , [Measures].[ActiveLearnerPercentPreviousYear] } ON COLUMNS ,
([MonthlyPeriod].[Calendar Period].[Month], [MonthlyPeriod].[Month No].[Month No]) ON ROWS
FROM [MonthlyMetrics]
WHERE (
      strtomember("[MonthlyPeriod].[Year].&[" + Format(now(), "yyyy") + "]")
      )



No comments:

Post a Comment