Question

# Calculating MovingAverage() treating blanks as zero's using ISDEFINED() times out

• 6 replies
• 138 views

+1
• Newly Minted Pigmenteer
• 4 replies

Hi,

I’m trying to calculate a moving average where blanks are counted as zero’s. When trying to leverage the “is defined” formula I get a calculation time-out error. Please advise.

### 6 replies

Userlevel 6
+14

Hi Gali,

Not sure what would lead to this situation - can you share the formula you’ve used, and the dimensions at play?

François

+1

Hi Francois,

I first tried using the IFDEFINED formula to replace blanks with zero’s within the calculation (see screenshot). Once that didn’t work (times-out) I tried creating an intermediate step where I replace blanks with zeros using the ISDEFINED formula, but that timed out as well (see screenshot). There are 6 dimensions involved.

Userlevel 6
+14

Hi Gali,

Without deeper knowledge about your dimensions / business case it’s hard to give a specific solution. However, it appears you’ve densified the calculation with the IF function, which is why you’re encountering a timeout.

Since Pigment is a sparse engine, it works really well when you only compute the cells that actually matter to you - e.g. if your metric is by Country x Region, it makes only sense to compute the intersection France - Europe and not France - North America.

When using the IF function, it is very tempting to add a 0 as the third argument to fill the rest of the cells with 0 and making sure all blank cells are counted as 0 - but it is also very poor on the performance side, because you’re computing every single cell intersecting your 6 dimensions - and I believe this is causing the timeout.

While I agree it would be nice to compute the MOVINGAVERAGE function counting cells bordering those with values, your best bet is to fill only those cells with 0s - this probably means making a mask / empty metric

Here’s a small spreadsheet model describing how I’d do it:

The mask does not fill all cells with 0s, but only where it is relevant for our business case - this could be based months of activity for each country, or based off the Actual Data using a SHIFT function for example.

As a general rule of thumb - the less data you compute, the faster it gets. Try minimising the size of your mask to make sure you only get what you need!

Hope this helps,

François

Userlevel 2
+4

@Gali Baram An alternative I’ve used for this is the movingsum formula and then dividing by the window size.  So the syntax looks something like:

`MOVINGSUM(Input, Window Size [, End Offset] [, Dimension]) / Window Size`

Hope this helps,
Justin

@Gali Baram ,
Have you tried it this way,
You use the ‘IFBLANK’ function,
Which would replace your formula as do: “IFBLANK(x,0)”?
It reduces multiple steps to one step, and should speed up your formula.

Here is the documentation on IFBLANK:

+1

Thanks for your help @Justin Groover and @Atlasi ! I was able to find a workaround for this