Published on InterSystems Developer Community (https://community.intersystems.com)

Home > BI (DeepSee) - calculate on pivot variable value

Question
Eduard Lebedyuk · 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?

#Analytics #Analyzer #InterSystems IRIS
Product version: IRIS 2020.1

Source URL:https://community.intersystems.com/post/bi-deepsee-calculate-pivot-variable-value