Question
Eduard Lebedyuk · Jun 4

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
00
1 0 3 57
Log in or sign up to continue

Hi Ed!

if you do it like this?

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]

Thank you. Looks like exactly what I need.

Does DSW3 work with pivot variables?

It did work. Not sure if it does now. But it's easy to test with ZPM ;)