turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- Which formula to use?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-25-2016 01:40 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to kcevans

10-25-2016 03:59 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Rick_SAS

10-27-2016 12:12 PM - edited 10-27-2016 12:13 PM

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!