Exclude Zeros From Average

I am calculating an average but I do not want the zero values to be included in the average. How can I exclude them?

Accepted answers

You can do this by adding a line item to count the values you want to include, i.e.: add a line item 'Count' = If 'Value' <> 0 then 1 else 0. Then you can calculate the average with a formula that is 'Value' / 'Count'. You will need to change the summary type of this line item to either 'Formula' or 'Ratio'. If you choose ratio, you need to specify the Numerator / Denomator below, so you would select 'Value' / 'Count' in this case.

All comments

PrevContributor

lokeshsinghal70

How to SUM the last 4 non zero values in the time series???

Example - if i have a data as -

12,0,4,0,8,12,9,0,5,0,3,5,7,0,3

Answer should be 18

12,0,4,0,8,12,9,0,5,0,3,0,7,3,3

Answer should be 16

Any ideas????

bhill5

not entirely elegant but....you'll need to use a "signal row" = if number row <> 0 then 1 else 0….next add calc row that divides ‘number row’ by "signal row" (the count)…and in blueprint mode change the calc row from sum to ‘formula’.

See attached pictures

Screen_Shot_2019-07-01_at_12_48099.png

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…