5 Replies Latest reply on Aug 13, 2018 6:21 AM by Naveen B

# Averages in LoD Calculations

Hi y'all,

I'm guessing I'm missing something blindingly obvious here, but I've been pulling apart and recombining several LoD Calcs to try and do what should be a very simple calculation.

I have the following data split by Month and year (Or technically Relative Months & Years). Column 3 is a Fixed LoD calc showing the Sum for the months at Month/Year Level.   It works.

Column 4 is a Fixed LoD calc showing the Average across the entire year.   That is incorrect; it should be 4433.

What is happening is it is including the number of records in the calculation (because of other fields I have not brought into the equations). So the actually calculation it is performing is:

((16600*7)+(311*6)+(198*6)+(623*7))/26

instead of

(16600+311+198+623)/4

I'm sure there is an obvious solution here that I'm missing because I can't believe it's this hard to find an average of sums at different LoDs without the values being thrown out because of ignored fields. What am I missing?!?!

• ###### 1. Re: Averages in LoD Calculations

Hi Andrew,

write a calculation like below

{fixed  yearpass : avg(monthly level)}

This would do if it dint help kindly attach the workbook with sample data

If it helps kindly mark this answers correct/helpful so that it will be helpful to others

BR,

NB

• ###### 2. Re: Averages in LoD Calculations

Hi NB - thanks for replying.

That formula is the exact formula in column 4 that is producing the incorrect result (For the reasons I stated)

Unfortunately, I cannot share the workbook as it contains confidential information - but the data in the table should be all you need.

Cheers

• ###### 3. Re: Averages in LoD Calculations

Window_sum(sum(Monthlyto))/window_count(count(order))

Try this once

• ###### 4. Re: Averages in LoD Calculations

Ok cheers, that seems to work....

I've never used window calcs before - does this mean that I'll have to continually edit the calculation when i'm using different views, like a rank() or Index()?

• ###### 5. Re: Averages in LoD Calculations

Since i didn't see how the view structured based on the Image given the Logic

You can default the table calculation in the calculated field itself

Hope this helps kindly mark this answer as correct or helpful so that it will help others

BR,

NB