Tuesday, November 29, 2016

MDX query for parallel period

I have been trying to figure out parallel period queries in MDX, and they seem to work nice, however I noticed another way of achieving similar results and just noting it here for my future reference.

Solution 1

The first solution is to declare members using "Cousine" function and specify "Ancester".

WITH 
  MEMBER [Measures].[Transaction of Selected Date] AS 
    (
      [Date].[Calendar].CurrentMember
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
  MEMBER [Measures].[Transaction of Previous Year's same Date] AS 
    (
      Cousin
      (
        [Date].[Calendar].CurrentMember
       ,Ancestor
        (
          [Date].[Calendar].CurrentMember
         ,[Date].[Calendar].[Calendar Year]
        ).Lag(1)
      )
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
SELECT 
  {
    [Measures].[Transaction of Selected Date]
   ,[Measures].[Transaction of Previous Year's same Date]
  } ON COLUMNS
FROM [Adventure Works]
WHERE 
  [Date].[Calendar].[Date].[September 1, 2003];

Ref: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e26f056f-1bc3-49e4-8733-fe11d15cafec/parallel-period-of-a-date-range?forum=sqlanalysisservices

Solution 2

Second solution is to declare members using "ParallelPeriod" function.

WITH 
  MEMBER [Measures].[Last Year Data] AS 
    (
      ParallelPeriod
      (
        [Date].[Calendar].[Calendar Year]
       ,1
       ,[Date].[Calendar].CurrentMember
      )
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
SELECT 
  {
    [Measures].[Reseller Sales Amount]
   ,[Measures].[Last Year Data]
  } ON COLUMNS
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].&[2003];

Solution 3

Third solution is to declare members using only "Lag" function.

WITH 
  MEMBER [Measures].[Last Year Data] AS 
    (
      [Date].[Calendar].CurrentMember.Lag(1)
     ,[Measures].[Reseller Sales Amount]
    ) 
   ,FORMAT_STRING = "Currency"
SELECT 
  {
    [Measures].[Reseller Sales Amount]
   ,[Measures].[Last Year Data]
  } ON COLUMNS
FROM [Adventure Works]
WHERE  ([Date].[Calendar].[Calendar Year].&[2003]);


Ref: http://aniruddhathengadi.blogspot.co.uk/2011/03/retrieve-selected-year-data-and-same.html

Thursday, November 24, 2016

Javascript date range of last three quarters and current quarter till yesterday

This is pretty small code, but took me a while to figure it out. So mentioning here just in case I would need to use it in the future.

// Show last 3 quarters (plus this quarter)
endDate = new Date();
var noOfQuarters = 3;
var quarter = Math.floor((endDate.getMonth() + 3) / 3);
startDate = new Date(endDate.getFullYear(), endDate.getMonth() - (noOfQuarters * 3 + endDate.getMonth() - (quarter - 1) * 3));

Secure micro services using jwt and ocelot

  Secure Microservices Using JWT With Ocelot in .NET Core (code-maze.com)