TIMESUM Function with aggregation method AVERAGE

Hi,

I am using TIMESUM function with AVERAGE as aggregation method for specific from-to period. Some of the cells have the zero values in it, but this functions is considering those zero cells also into count . This gives me wrong average. How can we skip zero cells and calculate average only for non zero values with specific time period range. See attached example average of below with TIMESUM function is showing as 115 instead of 172.5. I hope someone can help me here. Thanks in advance.

Best Regards,

Suman.

TIMESUM_Example_73957.JPG

Accepted answers

All comments

jasonblinn

Can you do this using the Ratio Summary Method?

I have 3 line items:

Entry: This is equal to the line in your screenshot

Timesum: This is the target line item where you are doing your timesum, and need your summary to be 172.5

Denominator: This is just counting the number of entries that we have that are not 0. So in this case the Formula says: IF Entry <> 0 THEN 1 ELSE 0

In the summary of the Timesum line, I have this:

I hope this helps!
Jason

Suman Reddy

Hi Jason,

Thank you for the suggestion. But i have to get the average only with TIMESUM() function instead of using summary methods. Because i do not have time dimension in result module.

Best Regards,

Suman.

abhi1017

Hi Suman,

Ratio is independent of time. You can use it in a module which doesn't have a time dimension as well.

@jasonblinn Has given an example for reference.

Alternative method if you want to go for timesum specifically.

Create one lineitem say Check ,boolean formatted; formula : If X>0 then true else false.[X is the line item with values]

Now, for Timesum lineitem :- If check then Timesum(,,,,) else 0

Hope this helps

~Abhi

Quick Links

Can you help?

Unanswered questions

Using Anaplan for Managing Custom Product Configurations (Example: Designer Dolls)
We’ve recently started using Anaplan to model and manage our supply chain and production planning for a niche ecommerce business that sells custom-designed dolls — each made-to-order with specific features, materials, and delivery lead times. A few interesting challenges we faced: SKU Explosion: Each doll can be customized…
OData Rest API for
Hi, Does anyone know how to get an outbound OData Rest API, (OAuth2) to establish a connection between SAP and Anaplan. Thanks Khaled
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…