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
Hi Ed!
if you do it like this?
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 ;)