?
Solved

formula check

Posted on 2011-04-26
34
Medium Priority
?
386 Views
Last Modified: 2012-05-11

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
Comment
Question by:tips54
  • 16
  • 15
  • +2
34 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35467752
(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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35467762
Also, what's with the +200+200+? Why not just do +400+? Should one of those be a *?
0
 
LVL 31

Expert Comment

by:GwynforWeb
ID: 35467810

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35467812
If you could explain the formula in English, we can help you get it into code.
0
 

Author Comment

by:tips54
ID: 35468308
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35469438
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
 

Author Comment

by:tips54
ID: 35469503
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35469612
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35469641
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35469657
Notice how that works no matter how many days are in the month. I think that may be what they are after.
0
 

Author Comment

by:tips54
ID: 35471092
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35471129
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 35471743
should this

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

be

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


0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35475245
No
0
 

Author Comment

by:tips54
ID: 35475462
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35475847
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
 

Author Comment

by:tips54
ID: 35476426
Any suggestion on how to achieve what i'm looking for?
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35476600
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
 

Author Comment

by:tips54
ID: 35477769
I don't want a projection. I just want to get an Average of those 4 months forecast values.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35477856
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
 

Author Comment

by:tips54
ID: 35478333

ok. what if we had 300 month 1, 400 month 2, 600 month 3 and 100 month 4.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35478369
(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
 

Author Comment

by:tips54
ID: 35478640

Yes That's it.  What if i'm 10 or 20 days into month 1?
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35478729
(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
 

Author Comment

by:tips54
ID: 35478924
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
 

Author Comment

by:tips54
ID: 35478939
this (datediff(d, getdate(), '8/1/2011')/datediff(d, '4/1/2011, '8/1/2011'))*(300+400+600+100)
gives me 0
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35479155
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
 
LVL 20

Expert Comment

by:thehagman
ID: 35479241
Do you mean that
(200*27/31 + 200 + 200 + 200)/(3 + 27/31)
gives something grossly different than 200?
0
 

Author Comment

by:tips54
ID: 35484270
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
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 600 total points
ID: 35484320
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
 

Author Comment

by:tips54
ID: 35484586
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
 

Author Comment

by:tips54
ID: 35484825
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
 

Author Comment

by:tips54
ID: 35485605
got it.  Copy and paste error
0
 

Author Closing Comment

by:tips54
ID: 35505770
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
Article by: evilrix
Looking for a way to avoid searching through large data sets for data that doesn't exist? A Bloom Filter might be what you need. This data structure is a probabilistic filter that allows you to avoid unnecessary searches when you know the data defin…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
Starting up a Project

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question