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.

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.

Page 1 / 1

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

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.

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

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

Hope this helps,

Justin

Have you tried it this way,

Where instead of using “IF(ISDEFINED(x),x,0)

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:

Thanks for your help

Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.