Count ZeroDataCells by Account Dimension Member

One approach to managing Zero Data Cells is to use the RemoveZeros funtion on the member formulas.

A simplistic example would be: api.data.calculate(A#TargetAccount = RemoveZeros(A#SourceAccount1 + A#SourceAccount2)")

Hi T_Kress ,

Thanks for the quick reply and suggestion. The "Data Record Analysis" solution was helpful for this.

While it took a bit of trial and error, I was able to back into calculated "ZeroData" cells by Account via the following steps (in case this is useful for anyone else looking to replicate the analysis).

1. Filter records with cell amounts that are >= -0.01 AND <= 0.01

2. Select the "IsRealData - Calculation" check box.

3. Select a sample of entities with the most data (to prevent timeout issue caused by querying all entities). An intermediate entity parent could be chosen as well.

4. Select the parent account under calculations in question reside

5. Run the "Detail" report view.  This will return all base intersections (i.e., if you select A#Top, it will show all accounts meeting the parameter criteria).

6. Export to CSV and remove and records with a cell amount of -0.01 OR 0.01 (this can potentially be avoided by filtering >=-0.001 AND <= 0.0.01). 

7. Create a Pivot Table by with AccountName in the rows and the "COUNT of Amount" in the values.

This allowed us to extrapolate and identify which account formulas are writing the most ZeroDataCells to the database. As acrespo alluded to, account member formulas producing ZeroDataCell values should be adjusted to utilize the "RemoveZeros" function.