TIMESUM Function with aggregation method AVERAGE

Highlighted
New Contributor

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.

3 REPLIES 3
Certified Master Anaplanner

Re: TIMESUM Function with aggregation method AVERAGE

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:

jasonblinn_0-1592321398122.png

jasonblinn_1-1592321486064.png

 

I hope this helps!
Jason

 

 

 

 

Highlighted
New Contributor

Re: TIMESUM Function with aggregation method AVERAGE

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.

Highlighted
Regular Contributor

Re: TIMESUM Function with aggregation method AVERAGE

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