Learning
Community
Open Exchange
Global Masters
InterSystems IRIS Data Platform 2019.3 / Application Development / InterSystems MDX Reference / MDX Functions / %CELLZERO
Previous section   Next section

%CELLZERO

Returns the value of another cell in a pivot table or returns zero if that cell has no value. This function is a Business Intelligence extension to MDX.

Returned Type

This function returns a number or a string.

Syntax and Details

%CELLZERO(relative_column_position,relative_row_position)
Where:
  • relative_column_position is an integer. Use 0 for the current column, –1 for the previous column (the column to the left), 1 for the next column (the column to the right), and so on.
  • relative_row_position is an integer. Use 0 for the current row, –1 for the previous row (the column above), 1 for the next row (the column below), and so on.
The system 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

Previous section   Next section