Desktop productivity for business analysts and programmers

Which formula to use?

New Contributor
Posts: 3

Which formula to use?

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.

Posts: 4,277

Re: Which formula to use?

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?

New Contributor
Posts: 3

Re: Which formula to use?

[ Edited ]

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!

Ask a Question
Discussion stats
  • 2 replies
  • 1 like
  • 2 in conversation