5 Replies Latest reply on Nov 21, 2016 11:59 AM by Sherzodbek Ibragimov

# Monthly Variance that has gaps in monthly data

Hello,

I am having a strange issue in calculating the difference between the Avg sales of a given month vs that of the last available month.

eg:

Jan - Avg Sales - 13

Feb - Avg Sales - 12; Variance =  -1

Oct - Avg Sales - 15: Variance = 3

In this case, i want to calculate the sales variance on a monthly calendar/months as X-axis and highlight/color code the variations only.

the months is between can be left blank as there were no sales in those months.

it will be great help if you can help me with the solution.

thanks,

Ranjit

• ###### 1. Re: Monthly Variance that has gaps in monthly data

Have you tried this:

LOOKUP(AVG(Sales),1)-LOOKUP(AVG(Sales),0)

• ###### 2. Re: Monthly Variance that has gaps in monthly data

Tried that but it does not work: The gap in the month disrupts. If there we no gaps, it would work perfectly fine:

for the above e.g. its coming as:

Jan - Avg Sales - 13 variance = Null

Feb - Avg Sales - 12; Variance =  -1

Oct - Avg Sales - 15: Variance = Null

Aug - Avg Sales - 19: Variance = 7

This is not correct

• ###### 4. Re: Monthly Variance that has gaps in monthly data

You can try this:

IF FIRST()=1 THEN 0

ELSE (LOOKUP(AVG(Sales),1)-LOOKUP(AVG(Sales),0)) END