1 2 3 Previous Next 31 Replies Latest reply on Aug 3, 2018 4:45 AM by Mahfooj Khan

# Calculating Average sales for preceding months

Hello everyone,

I have a month parameter which has values from Jan 18 to Sept 18. Currently i am in July month.

Logic:

1. Calculate the YTD  by averaging the values for preceding months
1. Example: It's July so I would expect my Sales to be an average of the monthly Sales values for Jan 2018, Feb 2018, Mar 2018, Apr 2018, May 2018, and Jun 2018.

Help me out.

Regards,

Koushik.

• ###### 1. Re: Calculating Average sales for preceding months

Hi koushik,

BR,

bharat

• ###### 2. Re: Calculating Average sales for preceding months

Hi, Koushik

Hope this helps

ZZ

• ###### 3. Re: Calculating Average sales for preceding months

Thanks ZZ & Bharath . I will check and let you know if i face any issues. I will keep as correct answer as well.

• ###### 4. Re: Calculating Average sales for preceding months

Hello, I am getting below error.

• ###### 5. Re: Calculating Average sales for preceding months

Hi,

Change you type of parameter on date.

D

• ###### 6. Re: Calculating Average sales for preceding months

I have done that. But Unable to achieve.Help me out.

Regards,

Koushik

• ###### 7. Re: Calculating Average sales for preceding months

can you provide a sample workbook?

ZZ

• ###### 8. Re: Calculating Average sales for preceding months

Hello ZZ, sorry for the late reply.

I need to create a month parameter.Example:  If i select March-18 my sales average should show me the average of Feb-18 and Jan-18.

Output: Month parameter: March-18

Country   Sales    Sales avg

US           300            150 (average of Jan & Feb)

Mahfooj Khan

Regards,

Koushik.

• ###### 9. Re: Calculating Average sales for preceding months

Hi,

Create a date parameter on your date field.

Create few calculations.

Final result:

Hope that helps.

~Tusahr

• ###### 10. Re: Calculating Average sales for preceding months

Hello Tushar, Thanks for the reply. I will check and revert. But if we have NULL values in  a month instead of numbers then?

regards,

Koushik.

• ###### 11. Re: Calculating Average sales for preceding months

Koushik,

You can use a ZN function... to handle nulls

BR,

bharat

• ###### 12. Re: Calculating Average sales for preceding months

Hello Tushar, I am getting error like: Cannot mix aggregate and non aggregate values .

if month<month_parameter then sales.

end

regards,

Koushik.

• ###### 13. Re: Calculating Average sales for preceding months

Hi koushik,

Try replacing month by attr(month)

BR,

bharat

• ###### 14. Re: Calculating Average sales for preceding months

Tried with the above logic: if attr(month)<month_parameter then zn(avg(sales))

end. Getting NULL values.

1 2 3 Previous Next