topic Re: Which formula to use? in SAS Enterprise Guide

Which formula to use?

kcevans — Tue, 25 Oct 2016 17:40:01 GMT

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.

Re: Which formula to use?

Rick_SAS — Tue, 25 Oct 2016 19:59:44 GMT

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?

Re: Which formula to use?

kcevans — Thu, 27 Oct 2016 16:13:43 GMT

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!