Exclude leading months from calculation that have zero demand.

[#1]

I am trying to calculate an average of demand. However, while calculating average, leading months that have zero demand should be excluded from calculation. Lets say, If I have 24 months of demand data. First two months have zero, Next 3 months have demand data and remaining months also have zero. I want to take an average of demand by excluding first two months that have zero. How do i do that ?

[#2]

You need a numeric month, obviously.

Try this:

Average = Sum(Demand) / (Max(Month) - Min({ <Month = {"= Sum(Demand) > 0"}> } Month))

Basically you divide the total of demand by the difference between the maximum month and the minimum, selected only between the ones having Sum(demand) > 0.

Hope it works! (let me know if it does!)

[#3]

Thank you Chesia,

However, the formula is not working for me specially (Max(Month) - Min({ <Month = {"= Sum(Demand) > 0"}> } Month)) part.

Currently i am working on QV version 8.20 so i am worndering if it is due to version issue.

Here is formula i am trying to write. The problem with this formaula is; it is excluding all zero months from calculation. I want to exlcude only first two months that have zero.

Sum

(Demand)/Count(All(Aggr(Max(Date)-min(if((Demand)>0,Date)),Date)))

[#4]

If you're on 8.2 you can't use Set Analysis... 😞

Try this:

Demand / (Max(Date) - $(=min(if(Demand>0, Date)))

Date should be the number of the month!

[#5]

Sorry:

Sum(Demand) / (Max(Date) - $(=min(if(Demand>0, Date)))

[#6]

Ok. I'm too dumb.

Even dollar sign was introduced in 8.5...

So you have to put the min expression in a variable and reference the variable in your formula.

[#7]

Thanks for the help. Howerver the formula seems to be working only if there are no more than 12 months data.

I think we need to tweak the formula to work for 24 months.... or might me i am missing something !!!!

[#8]

its working for 12 months only because you are using monthnum from 1 to 12.

use this - Year(Date) & Right(Left(Date, 5), 2) if your date is in a 'DD/MM/YYYY' format.

this along with chiesa80's formula should work.

[#9]

Thank you. Following expression seems to be working at this time. Thank you for helping me to fix my problem. Please respond to me if you think following expression is not going to work in the long run.

=Sum(demand)/

Floor((MAx(Date)-min(if([demand]>0,Date)))/30)+1