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)

Wednesday, January 7, 2015

MDX Named Set to Get Months of Current and Previous Years

MDX to Create Named Set to Get Months of Current and Previous Years



WITH
SET [Previous Year Months]
AS (StrToMember('[MonthlyPeriod].[Calendar Month].[Year].&['+ CSTR(CINT(FORMAT(NOW(), "yyyy"))-1) + '].&[1]'):StrToMember('[MonthlyPeriod].[Calendar Month].[Year].&['+ CSTR(CINT(FORMAT(NOW(), "yyyy"))-1) + '].&[12]'))
SET [Current Year Months]
AS (StrToMember('[MonthlyPeriod].[Calendar Month].[Year].&['+ FORMAT(NOW(), "yyyy") + '].&[1]'):StrToMember('[MonthlyPeriod].[Calendar Month].[Year].&['+ FORMAT(NOW(), "yyyy") + '].&[12]'))
SELECT [Previous Year Months] ON COLUMNS
FROM [CUBE]

No comments:

Post a Comment