Question
· Jun 4, 2021

BI (DeepSee) - calculate on pivot variable value

Is there a way to add/substract from a pivot variable?

I have this MDX:

SELECT
  NON EMPTY [DateOfSale].[Actual].[YearSold].&[$variable.Year] ON 0,
  NON EMPTY [Product].[P1].[Product Name].Members ON 1
FROM [HOLEFOODS]

And with pivot variable Year equal to, say, 2016 it works:

 

Now I want to add previous year  first with one pivot variable and one explicit reference. It also works:

SELECT
  NON EMPTY
  {
    [DateOfSale].[Actual].[YearSold].&[$variable.Year],
    [DateOfSale].[Actual].[YearSold].&[2015]
  } ON 0,
  NON EMPTY [Product].[P1].[Product Name].Members ON 1
FROM [HOLEFOODS]

 

Finally, I want both values to be taken from a pivot variable:

SELECT
  NON EMPTY
  {
    [DateOfSale].[Actual].[YearSold].&[$variable.Year],
    [DateOfSale].[Actual].[YearSold].&[$variable.Year-1]
  } ON 0,
  NON EMPTY [Product].[P1].[Product Name].Members ON 1
FROM [HOLEFOODS]

 

However the second set [$variable.Year-1] is empty. How do I add/subtract from the value of a pivot variable?

Product version: IRIS 2020.1
Discussion (3)1
Log in or sign up to continue