Which formula to use?

[kcevans]

I have posted a before and after look. 

In the after I have highlighted the formula I am trying to create. 

Trying to not only do a variance line but also a YTD variance..

So right now we are in period 5 (Oct) Next time I run it I will be in period 6 (Nov)

I am not at all sure what formula to use.. I have tried an iif statement and that didn't work..

help please.

[Rick_SAS]

To a statistician, variance is always positive. It looks like you are just subtracting one year from another.  (That might be the way accountants define variance, I don't know.) 

The formulas for most descriptive statistics in SAS are in the documentation.

Recall that if you have n nonmissing values, you can compute the variance of those numbers by 

1) Compute the mean Xbar.

2) Compute the n deviances: x[i] - Xbar

3) Sum the squared deviances: sum( (x[i] - Xbar)**2)

4) Divide by n-1

It's not clear to me what you are trying to accomplish. For what number are you trying to find the variance? The differences?

[kcevans]

Thank you so much for your reply!

Sorry new here I just now saw that you replied. 

In the first spreadsheet "Before" I don't have the variance as displayed in "After" which definition for variance (highlighted in yellow) for this purpose being:

Fiscal year 2017 (Periods) Diamonds - Fiscal Year 2016 (Periods) Diamonds = how many more/less diamond orders we have for the current period "month" in correlation to the same period last year.

Also, Fiscal year 2017 (Periods) Pearls - Fiscal Year (Periods) 2016 Pearls = how many more/less Pearl orders we have for the current period "month" in correlation to the same period last year.

But I only want the variance to display through current period I don't want it to calculate for months that have not occurred yet.  So for example period 6 (November) has not occurred yet so it would be blank (See After example highlighted in yellow).

This is easy for me to do in an excel spreadsheet but I need to build it in Enterprise Guide. 

Thank you again for all your assitance!