Hello,

I would like to do the average by counting blank value as 0.

How can I do that ?

Thanks,

Alexandre

Hello,

I would like to do the average by counting blank value as 0.

How can I do that ?

Thanks,

Alexandre

Page 1 / 1

Hi Alexandre,

Someone else may have a better idea but the quickest way to achieve this I can think of is just using IFBLANK([metric],0)

This will replace the blanks with 0’s for the calculation

IFBLANK documentation;

I hope this helps!

Hi Alexandre,

Please be mindful that Pigment remains a sparse engine - this means it only calculates values where there is no blank. For your application to work as fast as possible, you’ll have to try and reduce the number of cells calculated everywhere you can - only calculate / store what makes sense for your business.

While

To find out whether your block is dense or not, you can use the COUNTOF / COUNTALLOF functions: `COUNTOF(Metric) / COUNTALLOF(Metric)`

will give you an idea of how many zeros you’re adding in the calculation.

In a sparse calculation, you might want to avoid using IFBLANK, using another calculation to fill the zeros in, like this for example:

`IF(ISDEFINED(MetricIREMOVE: Month]), IFBLANK(Metric, 0))`

This will only fill zeros on **countries** where you have values (the total on months of each intersection has a value) - I recommend you explore with different modifiers to make your averages work while keeping performance as snappy as possible.

Hello François and Kieran, thank you for your tips ! Indeed, I have many dimensions with lots of blanks.

I’ll try François’ formula

Hi people,

I’m encountering the same issue, but I’m trying to calculate monthly averages of a transaction list, instead of a metric: so I can’t use IFBLANK or ISDEFINED.

The transaction list has +5 Million rows, so adding 0’s to the transaction list is out of the question.

How can I calculate an average where our loaded data itself is sparse (to save computation power)?

Thanks

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