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

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

@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, 
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 @Justin Groover and @Atlasi ! I was able to find a workaround for this