• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

formula check


Can someone please tell me what is wrong with this formula?

 select (200 * (31- datepart (d,( getdate()))/31)
+ 200 + 200 +
datepart (d,( getdate()))/31*200
) /31 as MonthlyForecast

I get a return of 212.  I was told the value should not be more than 200
using Lever law to get an average over 4 months.
0
tips54
Asked:
tips54
  • 16
  • 15
  • +2
1 Solution
 
TommySzalapskiCommented:
(31- datepart (d,( getdate()))/31)

That part has a problem. You divide the day of the month by 31 (which gives a value from 0 to 1) then you subtract it from 31 giving you a number always from 30 to 31.
Without having any idea what you are really trying to do, I would guess that should be
(1- datepart (d,( getdate()))/31)
So it's still scaled from 0 to 1.
0
 
TommySzalapskiCommented:
Also, what's with the +200+200+? Why not just do +400+? Should one of those be a *?
0
 
GwynforWebCommented:

For clarity putting d,( getdate())) = Y

you get

(200 * (31- Y/31)+ 200 + 200 +Y/31*200) /31

=(31*200-200*Y/31 +400+200*Y/31)/31

=(31*200+400)/31

= ((31 * 200) + 400) / 31 = 212.903226

ie it is always the same.

are you sure Y/31*200 should not be Y/(31*200)?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
TommySzalapskiCommented:
If you could explain the formula in English, we can help you get it into code.
0
 
tips54Author Commented:
thank you all.
Basically what we are trying to do is get an average of monthly forecast data.
for Item X the forecast is 200 this month, 200 for May, 200 June, 200 July. Instead of doing an average of the 4 values,  We want to use April's base on the current day and the same for the 4th months.

an other words weight of April is base on where we are in the month.  Level Law
0
 
TommySzalapskiCommented:
So let's say today is the 26th. What should the value be?

( 200*(26/30) + 600 )/4 ? That doesn't really make sense. I don't know what you mean by 'Level Law'
0
 
tips54Author Commented:
no.
The formula I was given is:

X = [ 300 [ 31-today /31] + 400 + [today/31] 200 ] / 31

when I put that in in code I get 212, but I was told that is incorrect
0
 
TommySzalapskiCommented:
Again 31 - today/31 cannot be right. It must mean 1 -  today/31 or (31-today)/31 because that gives a ratio representing the percent of the month that remains.
Dividing the whole thing by 31 again doesn't make sense either. Should that be a 4?
I think they may have meant
X = [ 200 [1-today/31] + 400 + 200 [today/31] ] / 4
But that still doesn't make sense because it will give you 150 every time since 1-today/31 ... + today/31 = 1

Also, what if a month has 30 days? (or 28 or 29)
0
 
TommySzalapskiCommented:
Do they just want the projection for what has passed so far?
I propose
200*4*(datediff(d, '4/1/2011',getdate())/datediff(d, '4/1/2011, '8/1/2011'))

That will give the percent of the total projection that you should have seen so far. Or you might want the total projection remaining:
200*4*(datediff(d, getdate(), '8/1/2011')/datediff(d, '4/1/2011, '8/1/2011'))
0
 
TommySzalapskiCommented:
Notice how that works no matter how many days are in the month. I think that may be what they are after.
0
 
tips54Author Commented:
Hey Tommy,

I just got a little more clarification.
What we are trying to get is the percentage of the remainder of days for the current month and the number of days difference for the last month.

so today is the 26. 4days left: so we want fraction of 4/31 * the first value + 200 +200 +  26/31 of the last
0
 
TommySzalapskiCommented:
But (as Gwyn mentioned earlier) x/31 + (31-x)/31 = 1 always. So you will always get 300 every time.
Also you still have the problem of some months not having 31 days. Whoever is telling you to do this is either not explaining it well or is confused.

What is that number supposed to accoplish? I still stick by my recommendation of looking at the percent of time that has passed for the whole period.
0
 
Arthur_WoodCommented:
should this

X = [ 300 [ 31-today /31] + 400 + [today/31] 200 ] / 31

be

X = [ 300*[ (31-today) /31] + 400 +  200*[today/31]] / 31


0
 
TommySzalapskiCommented:
No
0
 
tips54Author Commented:
Tommy,

"Do they just want the projection for what has passed so far?"
no.
We have a software that is forecasting inventory for the next 4 months including the current month. We are extracting an Average of those 4 months for a  report, but instead of getting a straight average,  we want a fraction of the current month base on days left plus the forecast number for the 2 months in the middle and a fraction of the last month Divided by 3.

--
The thing is the person that is explaining this is doing on paper, so when they do remaining days of the month divided by 31 (i.e. 3/31), they get a decimal number and you do 200 times that decimal number  + 400 + decimal value of 27/31  time 200 Then divide all of it by 3.
That is the peace I was missing  I was originally dividing by 31.

0
 
TommySzalapskiCommented:
Again 200*(3/31) + 200*(27/31) = 200*(30/31)
Or      200*(10/31) + 200*(21/31) = 200*(30/31)

So no matter what day it is, the answer will always be (200 + 200 + 200*30/31)/3.

Show this result to the person giving you the formula and explain that it makes no sense. Why in the world is the last month a fraction? Shouldn't the only fraction be this month?
0
 
tips54Author Commented:
Any suggestion on how to achieve what i'm looking for?
0
 
TommySzalapskiCommented:
If you use 200 as the base and then get a fraction of the 200 and weight that with the same fraction, then you'll always end up with 200/mo

Are you trying to change the projection based on what you have so far this month? Like, let's say the month is half over and you already have 130, should the average be slightly higher than the 200? Like perhaps we want 130 + 100 projected remaining + 3*200 for the other months = 830/8 = 207.50 per month?

Or if the month is half over do you want 700 total projected remaining? I'm not sure what you are looking for so it's hard to suggest how to achieve it. If you are doing projections, then it makes sense to modify those projections based on the real data so that's what I would suggest.
0
 
tips54Author Commented:
I don't want a projection. I just want to get an Average of those 4 months forecast values.
0
 
TommySzalapskiCommented:
The average is 200. Always. If each month has a forecast of 200, then the forcast for 1/2 of a month is 1/2*200 so the forcast for each month is 200.

The forcast for 3.5 months is 200*3.5 so if you want total remaining forcast, you could use my earlier formula to get the percentage of days remaining, but the average forcast will always be 200/mo.
0
 
tips54Author Commented:

ok. what if we had 300 month 1, 400 month 2, 600 month 3 and 100 month 4.
0
 
TommySzalapskiCommented:
(300+400+600+100)/4 = 350
Halfway through month 1 remainder is (150+400+600+100)/3.5 = 357.14
Is that what you are going for?
0
 
tips54Author Commented:

Yes That's it.  What if i'm 10 or 20 days into month 1?
0
 
TommySzalapskiCommented:
(300*datepart(d, getdate())/31 + 400 + 600 + 100)/(3 + datepart(d, getdate())/31) (which is the same as [300*10/31 + 400 + 600 + 100]/[3+10/31] if you are 10 days in.
If you are fine with assuming 31 days. Otherwise you'll need something complicated to calculate the total days in the month. You could make a function; the code is out there, just Google for it.

Or you could multiply the total by the total percent of time not yet passed like:
(datediff(d, getdate(), '8/1/2011')/datediff(d, '4/1/2011, '8/1/2011'))*(300+400+600+100)

0
 
tips54Author Commented:
base on below if I have 200 for each month shouldn't I get 200. instead I get 258
"(300*datepart(d, getdate())/31 + 400 + 600 + 100)/(3 + datepart(d, getdate())/31) "
0
 
tips54Author Commented:
this (datediff(d, getdate(), '8/1/2011')/datediff(d, '4/1/2011, '8/1/2011'))*(300+400+600+100)
gives me 0
0
 
TommySzalapskiCommented:
I guess you need to cast it to a double. It must be doing integer division

(200*datepart(d, getdate())/31 + 200 + 200 + 200)/(3 + datepart(d, getdate())/31)
=(200*27/31 + 600)/(3 + 27/31) = 774/3.87 = 200
Are you sure you didn't leave a 300 or something?
0
 
thehagmanCommented:
Do you mean that
(200*27/31 + 200 + 200 + 200)/(3 + 27/31)
gives something grossly different than 200?
0
 
tips54Author Commented:
I got what I wanted by doing the following:

 ((((31- datepart (d,( getdate())))*1.0/31) *200)+200+200+ (((datepart (d,( getdate())))*1.0/31) *200) )/3

but i'm getting one corky thing that's throwing me for a loop.
 ((((31- datepart (d,( getdate())))*1.0/31) *150)+0+0+ (((datepart (d,( getdate())))*1.0/31) *150) )/3

is giving me 100 when I used the values that is in the sql fields. ???
0
 
TommySzalapskiCommented:
I don't see how you could get 100. It should be much higher. The 31- datepart (d,( getdate())))*1.0/31) is going to be over 30 every time. Are you sure you didn't actually do
1- datepart (d,( getdate())))*1.0/31)
or
(31- datepart (d,( getdate())))*1.0) /31)
?
0
 
tips54Author Commented:
I'm seeing a pattern. i only get that discrepancy when there's a zero in one of the two middle months values.  it works for 98% of the line  items except for when there's a zero in

When I key in the values in the formula I mentioned I do get 49.99.  
i.e.  ((((31- datepart (d,( getdate())))*1.0/31) *200)+200+200+ (((datepart (d,( getdate())))*1.0/31) *200) )/3

0
 
tips54Author Commented:
sorry I meant:

i.e.
Select  ((((31- datepart (d,( getdate())))*1.0/31) *150)+0+0+ (((datepart (d,( getdate())))*1.0/31) *150) )/3
= 49.99
0
 
tips54Author Commented:
got it.  Copy and paste error
0
 
tips54Author Commented:
Thanks Tommy.

I will award you the points for helping me get to this point and your constant feedback.

solution:
 ((((31- datepart (d,( getdate())))*1.0/31) *200)+200+200+ (((datepart (d,( getdate())))*1.0/31) *200) )/3
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 16
  • 15
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now