DeepSee MDX Reference
%CELLZERO
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

Returns the value of another cell in a pivot table or returns zero if that cell has no value. This function is a DeepSee extension to MDX.
Returned Type
This function returns a number or a string.
Syntax and Details
%CELLZERO(relative_column_position,relative_row_position)
Where:
DeepSee returns the value of the given cell, or zero if the cell has no value.
Example
The following query uses three measures. The first measure displays the number of units sold in a given period. The second measure displays the number of units sold in the previous period; this is a calculated measure defined with PREVMEMBER. The third measure displays the change in units sold since the previous period; this is a calculated measure defined with %CELLZERO.
WITH  MEMBER [MEASURES].[UnitsSoldPreviousPeriod] 
AS '%LABEL(([DateOfsale].[Actual].CurrentMember.PrevMember,MEASURES.[units sold]),"Units (Prev Period)")' 
MEMBER [MEASURES].[Delta Since Prev Period] AS '%CELLZERO(-2,0)-%CELLZERO(-1,0)' 
SELECT 
{[Measures].[Units Sold],[MEASURES].[UNITSSOLDPREVIOUSPERIOD],[MEASURES].[DELTA SINCE PREV PERIOD]} ON 0,
[DateOfSale].[Actual].[MonthSold].Members ON 1 
FROM [HoleFoods] 
%FILTER [PRODUCT].[P1].[PRODUCT CATEGORY].&[Dairy]
 
                        Units Sold   Units (Prev Peri   Delta Since Prev
 1 Jan-2009                       *                  *                  0
 2 Feb-2009                       *                  *                  0
 3 Mar-2009                       *                  *                  0
 4 Apr-2009                       1                  *                  1
 5 May-2009                       *                  1                 -1
 6 Jun-2009                       8                  *                  8
 7 Jul-2009                       1                  8                 -7
 8 Aug-2009                       *                  1                 -1
 9 Sep-2009                       *                  *                  0
10 Oct-2009                       *                  *                  0
11 Nov-2009                       *                  *                  0
12 Dec-2009                       *                  *                  0
13 Jan-2010                       1                  *                  1
14 Feb-2010                       *                  1                 -1
15 Mar-2010                       2                  *                  2
...
See Also