Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Calculating Forecast 1

Status
Not open for further replies.
arcticvman

arcticvman

MIS
Jan 28, 2003
18
0
0
US
I am working on a page that includes a sales forecast total.
The sales forecast is broken down by months and customer, so I sum the monthly total and use a quick calculation to get the prorated qty expected sold for the current month. What I need help with is adding the previous forecasted month total to that prorated number.
What I am doing to get the current month is below:
Code: 
$partno = '6010';
$month1=date("m");
$day=date("d");
$conn = odbc_connect('database','user','password');
if ($conn)
{
  $queryfytd = "select MONTH(due_dt) AS month, FLOOR(SUM(qty)) AS fytd FROM table WHERE MONTH(due_dt) = 10 AND item_no = '$partno' GROUP BY due_dt";
    $resultfytd=odbc_exec($conn,$queryfytd);
      while($rowfytd = odbc_fetch_array($resultfytd)){
          $fytd1 = $rowfytd['fytd'];
}
}
else echo "Hey Goober!  Check your connection.";
$fytdtotal = FLOOR((($fytd1 / 30) * $day))
?>
<table border=1 style=" font-family:arial;font-size:85%;color:black;">
<caption><B></B></caption>
<tr>
<th>QTY</th>
</tr>
<?PHP

echo "<td align=center>";
echo $fytdtotal;
?>

My year runs from Oct to Sep which is why my current method is ok, but once we roll into November I need to be able to add the Oct forecast number into the calculated number. For example, in January I will prorate the forecasted sales based on the current date and then I need to add the forecasted numbers from Oct-Nov-Dec to this number so then I can show quantity ahead or behind of forecast.

I hope this makes sense, if there are any questions for me please let me know and I will answer as quick as I can.

Thanks for all the help.
 
Sort by date Sort by votes
I don't know if there's a database issue, you can visit the appropriate forum to check.

One problem seems to be that you're resetting $fytd1 with each loop iteration and it looks like you want to accumulate values in that variable.
 
Upvote 0 Downvote
i don't really understand why you are running a loop etc. or even grouping, as you are simply looking for the total (seemingly). would a simple SUM not work with a where clause? also should you not be specifying a year in the where clause?

Code: 
$partno = '6010';
$conn = odbc_connect('database','user','password');
if (!$conn) die('problem connecting');
$queryfytd = <<<SQL
SELECT        FLOOR(SUM(qty)) AS fytd 
FROM          table 
WHERE        MONTH(due_dt) = 10 
                  AND
                  YEAR(due_dt) = 2011
                  AND 
                  item_no = '$partno' 
GROUP BY  due_dt
SQL;
$resultfytd=odbc_exec($conn,$queryfytd);
$rowfytd = odbc_fetch_array($resultfytd);
$fytd1 = $rowfytd['fytd'];
$fytdtotal = FLOOR((($fytd1 / 30) * $day))
?>
<table border=1 style=" font-family:arial;font-size:85%;color:black;">
<caption><B></B></caption>
<tr>
<th>QTY</th>
</tr>
<?PHP

echo "<td align=center>";
echo $fytdtotal;
?>

however i think i am not fully understanding the maths issue you are facing. perhaps you could take us through that step by step, providing a worked example.
 
Upvote 0 Downvote
The code I posted works for me now, it returns a single block of information, with a column name of quantity and the quantity it calculates based on the forcasted sum based on todays date.
Code: 
$partno = '6010';
$conn = odbc_connect('database','user','password');
if (!$conn) die('problem connecting');
$queryfytd = <<<SQL
SELECT        FLOOR(SUM(qty)) AS fytd,
              MONTH(due_dt) AS month
FROM          table
WHERE         MONTH(due_dt) = 10
              AND item_no = '$partno'
GROUP BY  due_dt
SQL;
$resultfytd=odbc_exec($conn,$queryfytd);
$rowfytd = odbc_fetch_array($resultfytd);
$fytd1 = $rowfytd['fytd'];
$fytdtotal = FLOOR((($fytd1 / 30) * $day))
?>
<table border=1 style=" font-family:arial;font-size:85%;color:black;">
<caption><B></B></caption>
<tr>
<th>QTY</th>
</tr>
<?PHP

echo "<td align=center>";
echo $fytdtotal;
?>
As you have already noticed I am still learning PHP so I am not sure what I need to do.
The reason I have a sum and group is that I am grouping by due_dt to get the sum for all the entries for that month.
I know I will have to adjust my query to get some other information, but wasn't sure how I would handle that information once I got it.
What I am wanting to do is to be able to still only show the single column header QTY with the calculated sum based on todays date AND any previous months total forecast.
So, if there was a forecast that was:
10 items in October, 15 in November, 30 in December and 30 in January and today was January 15, then the table would read
QTY
60
the sum of October, November, December plus the calculated total for January.
I hope this helps explain it a bit better.
Thanks
 
Upvote 0 Downvote
The reason I have a sum and group is that I am grouping by due_dt to get the sum for all the entries for that month.
Click to expand...
you should not need this. a simple where clause should be enough (unless mssql is very picky).
you really ought to include the year in your sql otherwise things will break down in future years and you will include quantities sold for 2011 in your 2012 calculations.

On your worked example, unless you did not mean that the quantity should be seventy
this query should give you what you want. I have no MSSQL installation so cannot test it, however.

Code: 
[COLOR=#990000 ]<?php[/color]
[b][COLOR=#000000 ]date_default_timezone_set[/color][/b][COLOR=#990000 ]([/color][COLOR=#FF0000 ]"UTC"[/color][COLOR=#990000 ]);[/color]
[COLOR=#009900 ]$today[/color] [COLOR=#990000 ]=[/color] [b][COLOR=#000000 ]date[/color][/b][COLOR=#990000 ]([/color][COLOR=#FF0000 ]'Y-m-d'[/color][COLOR=#990000 ]);[/color]
[COLOR=#009900 ]$start[/color] [COLOR=#990000 ]=[/color] [b][COLOR=#000000 ]date[/color][/b][COLOR=#990000 ]([/color][COLOR=#FF0000 ]'Y-m-01'[/color][COLOR=#990000 ],[/color] [b][COLOR=#000000 ]strtotime[/color][/b][COLOR=#990000 ]([/color][COLOR=#FF0000 ]'next month'[/color][COLOR=#990000 ],[/color] [b][COLOR=#000000 ]strtotime[/color][/b][COLOR=#990000 ]([/color][COLOR=#FF0000 ]'-1 year'[/color][COLOR=#990000 ])));[/color]
[COLOR=#009900 ]$n[/color] [COLOR=#990000 ]=[/color] [b][COLOR=#000000 ]intval[/color][/b][COLOR=#990000 ]([/color][b][COLOR=#000000 ]date[/color][/b] [COLOR=#990000 ]([/color][COLOR=#FF0000 ]'t'[/color][COLOR=#990000 ]));[/color]
[COLOR=#009900 ]$d[/color] [COLOR=#990000 ]=[/color] [b][COLOR=#000000 ]intval[/color][/b][COLOR=#990000 ]([/color][b][COLOR=#000000 ]date[/color][/b][COLOR=#990000 ]([/color][COLOR=#FF0000 ]'j'[/color][COLOR=#990000 ]));[/color]
[COLOR=#009900 ]$sql[/color] [COLOR=#990000 ]=[/color] [COLOR=#990000 ]<<<[/color]SQL
SELECT[tab][tab]  [b][COLOR=#000000 ]FLOOR[/color][/b][COLOR=#990000 ]([/color]
[tab][tab][tab][tab][tab][b][COLOR=#000000 ]SUM[/color][/b][COLOR=#990000 ]([/color]
[tab][tab][tab][tab][tab][tab]CASE
[tab][tab][tab][tab][tab][tab][tab][b][COLOR=#000000 ]MONTH[/color][/b][COLOR=#990000 ]([/color]due_dt[COLOR=#990000 ])[/color]
[tab][tab][tab][tab][tab][tab][tab]WHEN [COLOR=#009900 ]$m[/color]
[tab][tab][tab][tab][tab][tab][tab][b][COLOR=#000000 ]THEN[/color][/b] [COLOR=#990000 ]([/color][b][COLOR=#000000 ]SUM[/color][/b][COLOR=#990000 ]([/color]qty[COLOR=#990000 ])/[/color][COLOR=#009900 ]$n[/color][COLOR=#990000 ])[/color] [COLOR=#990000 ]*[/color] [COLOR=#009900 ]$d[/color]
[tab][tab][tab][tab][tab][tab][tab]ELSE [b][COLOR=#000000 ]sum[/color][/b][COLOR=#990000 ]([/color]qty[COLOR=#990000 ])[/color]
[tab][tab][tab][tab][tab][tab]END
[tab][tab][tab][tab][tab][COLOR=#990000 ])[/color]
[tab][tab][tab][tab][COLOR=#990000 ])[/color] AS fytdtotal
FROM[tab][tab][tab]table
WHERE[tab][tab]   due_dt BETWEEN [COLOR=#FF0000 ]'$start'[/color] AND [COLOR=#FF0000 ]'$today'[/color]
GROUP BY[tab][tab]due_dt
SQL[COLOR=#990000 ];[/color]
[COLOR=#009900 ]$resultfytd[/color][COLOR=#990000 ]=[/color][b][COLOR=#000000 ]odbc_exec[/color][/b][COLOR=#990000 ]([/color][COLOR=#009900 ]$conn[/color][COLOR=#990000 ],[/color][COLOR=#009900 ]$queryfytd[/color][COLOR=#990000 ]);[/color]
[COLOR=#009900 ]$rowfytd[/color] [COLOR=#990000 ]=[/color] [b][COLOR=#000000 ]odbc_fetch_array[/color][/b][COLOR=#990000 ]([/color][COLOR=#009900 ]$resultfytd[/color][COLOR=#990000 ]);[/color]
[COLOR=#009900 ]$total[/color] [COLOR=#990000 ]=[/color] [COLOR=#009900 ]$rowfytd[/color][COLOR=#990000 ][[/color][COLOR=#FF0000 ]'fydtotal'[/color][COLOR=#990000 ]];[/color]
[COLOR=#990000 ]?>[/color]
[COLOR=#990000 ]<[/color]table border[COLOR=#990000 ]=[/color][COLOR=#993399 ]1[/color] style[COLOR=#990000 ]=[/color][COLOR=#FF0000 ]" font-family:arial;font-size:85%;color:black;"[/color][COLOR=#990000 ]>[/color]
[COLOR=#990000 ]<[/color]caption[COLOR=#990000 ]><[/color]B[COLOR=#990000 ]></[/color]B[COLOR=#990000 ]></[/color]caption[COLOR=#990000 ]>[/color]
[COLOR=#990000 ]<[/color]tr[COLOR=#990000 ]>[/color]
[COLOR=#990000 ]<[/color]th[COLOR=#990000 ]>[/color]QTY[COLOR=#990000 ]</[/color]th[COLOR=#990000 ]>[/color]
[COLOR=#990000 ]</[/color]tr[COLOR=#990000 ]>[/color]
[COLOR=#990000 ]<[/color]tr[COLOR=#990000 ]>[/color]
[COLOR=#990000 ]<[/color]td align[COLOR=#990000 ]=[/color]center[COLOR=#990000 ]><?php[/color] [b][COLOR=#0000FF ]echo[/color][/b] [COLOR=#009900 ]$total[/color][COLOR=#990000 ];?></[/color]td[COLOR=#990000 ]>[/color]
[COLOR=#990000 ]</[/color]tr[COLOR=#990000 ]>[/color]
[COLOR=#990000 ]</[/color]table[COLOR=#990000 ]>[/color]
 
Upvote 0 Downvote
Thanks jpadie, I am trying it out now. On line 12 I am assuming that you meant $n not $m.
I will update you once I have it working on my end.

 
Upvote 0 Downvote
ah. no it should be $m. add this to the variable setters

Code: 
$m  = intval(date('n'));
 
Upvote 0 Downvote
thanks, I did already add that, once I was looking at it and was able to understand it myself I saw what you were doing.
Nice to know I guessed right.
I got a bit busy today with other things, and I am still working on this, but I will get back to it fully as soon as I can and will update when I have something.
Thanks again for all your help.
 
Upvote 0 Downvote
no worries. I will be offline for two weeks now but I am certain that any of the others on this forum will be able to pick up where I left off.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

milisql
  • Locked
  • Question
Fetching Data But not calculating Correctly
Replies
0
Views
148
milisql
milisql
notsoevilgenius
  • Locked
  • Question
Help Calculating Time Worked 1
Replies
6
Views
83
notsoevilgenius
notsoevilgenius
Borvik
  • Locked
  • Question
Calculating Freight/Shipping costs
Replies
3
Views
25
lgarner
lgarner
peterv6
  • Locked
  • Question
calculating dates
Replies
2
Views
31
peterv6
peterv6
newphpbie
  • Locked
  • Question
Calculating the time it takes to return a query
Replies
1
Views
17
sleipnir214
sleipnir214

Part and Inventory Search

Sponsor

Back
Top