Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 17th, 2006, 12:33 PM
Authorized User
 
Join Date: Dec 2004
Location: Sunman, IN, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default Calculate a difference percentage

I have an ASP pulling data from a database for display purposes.

I would like a spot in my table for the following type of calculation.

Code:
<%CDbl((SunCount("Mc")))-CDbl(PvsSunCount("Mc"))/CDbl(PvsSunCount("Mc")))"%>
I've created a form field with this as its value and get an Expected end of statement.

Here's the code generating the error.

Code:
<input name="textfield" type="text" value="<%CDbl((SunCount("Mc")))-CDbl(PvsSunCount("Mc"))/CDbl(PvsSunCount("Mc")))"%>" size="12" >

Any idea what I'm doing wrong?

 
Old March 18th, 2006, 01:15 AM
Wrox Technical Editor
 
Join Date: Dec 2005
Location: , , Canada.
Posts: 271
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What is the error message?

Could you post some sample data, sample output and the erroneous output?

- A.Kahtava
 
Old March 18th, 2006, 01:22 AM
Wrox Technical Editor
 
Join Date: Dec 2005
Location: , , Canada.
Posts: 271
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Have you taken operator precidence into consideration?

The division operator comes before subtraction.

Example:
    1-1/2 = 1/2
    (1-1)/2 = 0

VBScript Operator Precedence: http://ns7.webmasters.com/caspdoc/ht...precedence.htm

- A.Kahtava
 
Old March 19th, 2006, 11:54 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

As Adam said some sample data would be useful. In future to get a solution quickly always post the exact
syntax of the un-friendly HTTP error. If the error suggests a line number, post the code (problematic bits only)
indicating which line is the problematic one.

I can immediatly see three things wrong with your code:
;;;<input name="textfield" type="text" value="<%CDbl((SunCount("Mc")))-CDbl(PvsSunCount("Mc"))/CDbl(PvsSunCount("Mc")))"%>" size="12" >

1..that code if trouble fee would not write a value, you have no response.write (or the shortcut which is =) in there. You need:
value="<%= CDbl...
or
value="<% response.write CDbl...

2..You have a double quote at the end that does not need to be there:
"%>" size="12" >
should be:
%>" size="12" >

3..The number of opening opening brackets does not match the nuber of closing brackets - these need to match up. Count them, you have seven opening brackets and eight closing brackets.

FYI Problem number one will not cause an error, it wil just fail to write a value. Problems 2 and 3 are seperate problems and will each cause seperate errors.


Wind is your friend
Matt
 
Old March 20th, 2006, 10:03 AM
Authorized User
 
Join Date: Dec 2004
Location: Sunman, IN, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Recordset "Wk" data pulled from an Access Crosstab Query
RecDate Category GW Mc Xm
3/19/2006 Blank 76
3/19/2006 Printed 0 278 0

Recordset "PvsWk" data pulled from another Access Crosstab Query
RecDate Category GW Mc Xm
3/13/2005 Blank 108 0
3/13/2005 Printed 0 466 0
3/14/2005 Blank 353 0
3/14/2005 Printed 0 1695 0
3/15/2005 Blank 267 0
3/15/2005 Printed 0 1618 0
3/16/2005 Blank 205 0
3/16/2005 Printed 1 1448 0
3/17/2005 Blank 238 0
3/17/2005 Printed 0 1416 0
3/18/2005 Blank 230 0
3/18/2005 Printed 0 1345 0
3/19/2005 Blank 90 0
3/19/2005 Printed 0 585 0

I will have percentage fields to calculate.

Here's an example: 278 (Current Mc) 466 (Pvs Mc)
(278-466)/466= -40.34% Decrease

Here's my code on the ASP:
Code:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Dim Wk
Dim Wk_numRows

Set Wk = Server.CreateObject("ADODB.Recordset")
Wk.ActiveConnection = MM_McPOrderCount_STRING
Wk.Source = "SELECT * FROM qryWeek_Crosstab"
Wk.CursorType = 0
Wk.CursorLocation = 2
Wk.LockType = 1
Wk.Open()

Wk_numRows = 0
%>
<%
Dim PvsWk
Dim PvsWk_numRows

Set PvsWk = Server.CreateObject("ADODB.Recordset")
PvsWk.ActiveConnection = MM_McPOrderCount_STRING
PvsWk.Source = "SELECT * FROM qryPvsWeek_Crosstab"
PvsWk.CursorType = 0
PvsWk.CursorLocation = 2
PvsWk.LockType = 1
PvsWk.Open()

PvsWk_numRows = 0
%>
<%
Dim YTD
Dim YTD_numRows

Set YTD = Server.CreateObject("ADODB.Recordset")
YTD.ActiveConnection = MM_McPOrderCount_STRING
YTD.Source = "SELECT * FROM qry4YTDCrosstab"
YTD.CursorType = 0
YTD.CursorLocation = 2
YTD.LockType = 1
YTD.Open()

YTD_numRows = 0
%>
<%
Dim PvsYTD
Dim PvsYTD_numRows

Set PvsYTD = Server.CreateObject("ADODB.Recordset")
PvsYTD.ActiveConnection = MM_McPOrderCount_STRING
PvsYTD.Source = "SELECT * FROM qry4PvsYTDCrosstab"
PvsYTD.CursorType = 0
PvsYTD.CursorLocation = 2
PvsYTD.LockType = 1
PvsYTD.Open()

PvsYTD_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Wk_numRows = Wk_numRows + Repeat1__numRows
%>
<%
Dim Repeat2__numRows
Dim Repeat2__index

Repeat2__numRows = -1
Repeat2__index = 0
YTD_numRows = YTD_numRows + Repeat2__numRows
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<p align="center"><strong>Weekly Count Comparison</strong></p>
<table width="750" border="1" cellspacing="0" cellpadding="0">
  <tr valign="middle"> 
    <td width="270" valign="top"> 
      <table width="270" border="1" cellspacing="0" cellpadding="0">
        <tr> 
          <td bordercolor="#FFFFFF">&nbsp;</td>
          <td bordercolor="#FFFFFF">&nbsp;</td>
          <td colspan="3"> <div align="center">Cur Year</div></td>
        </tr>
        <tr bgcolor="#333333"> 
          <td> <div align="center">Date</div></td>
          <td> <div align="center">Category</div></td>
          <td> <div align="center">GW</div></td>
          <td> <div align="center">Mc</div></td>
          <td> <div align="center">Xm</div></td>
        </tr>
        <% 
While ((Repeat1__numRows <> 0) AND (NOT Wk.EOF)) 
%>
        <tr> 
          <td><div align="center"><%=(Wk.Fields.Item("RecDate").Value)%></div></td>
          <td><div align="center"><%=(Wk.Fields.Item("Category").Value)%></div></td>
          <td><div align="center"><%=(Wk.Fields.Item("GW").Value)%></div></td>
          <td><div align="center"><%=(Wk.Fields.Item("Mc").Value)%></div></td>
          <td><div align="center"><%=(Wk.Fields.Item("Xm").Value)%></div></td>
        </tr>
        <% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  Wk.MoveNext()
Wend
%>
        <% 
While ((Repeat2__numRows <> 0) AND (NOT YTD.EOF)) 
%>
        <tr> 
          <td><div align="center">YTD</div></td>
          <td><div align="center"><%=(YTD.Fields.Item("Category").Value)%></div></td>
          <td><div align="center"><%=(YTD.Fields.Item("GW").Value)%></div></td>
          <td><div align="center"><%=(YTD.Fields.Item("Mc").Value)%></div></td>
          <td><div align="center"><%=(YTD.Fields.Item("Xm").Value)%></div></td>
        </tr>
        <% 
  Repeat2__index=Repeat2__index+1
  Repeat2__numRows=Repeat2__numRows-1
  YTD.MoveNext()
Wend
%>
      </table></td>
    <td width="210" valign="top"> 
      <table width="210" border="1" align="center" cellpadding="0" cellspacing="0">
        <tr> 
          <td colspan="3"> <div align="center">Pvs Year</div></td>
        </tr>
        <tr bgcolor="#333333"> 
          <td width="70"><div align="center">GW</div></td>
          <td width="70"><div align="center">Mc</div></td>
          <td width="70"><div align="center">Xm</div></td>
        </tr>
        <tr> 
          <td width="70"><div align="center"><%=(PvsWk.Fields.Item("GW").Value)%></div></td>
          <td width="70"><div align="center"><%=(PvsWk.Fields.Item("Mc").Value)%></div></td>
          <td width="70"><div align="center"><%=(PvsWk.Fields.Item("Xm").Value)%></div></td>
        </tr>
        <tr> 
          <td><div align="center"><%=(PvsYTD.Fields.Item("GW").Value)%></div></td>
          <td><div align="center"><%=(PvsYTD.Fields.Item("Mc").Value)%></div></td>
          <td><div align="center"><%=(PvsYTD.Fields.Item("Xm").Value)%></div></td>
        </tr>
      </table>

    </td>
    <td width="254" valign="top">
<table width="210" border="1" cellspacing="0" cellpadding="0">
        <tr> 
          <td colspan="3"><div align="center">Percentage</div></td>
        </tr>
        <tr bgcolor="#333333"> 
          <td> 
            <div align="center">GW</div></td>
          <td> 
            <div align="center">Mc</div></td>
          <td> 
            <div align="center">Xm</div></td>
        </tr>
        <tr> 
          <td width="70">
<div align="center"> </div></td>
          <td><div align="center">
              <input name="textfield" type="text" 
              value="<%=CDbl((Wk("Mc")))-CDbl(PvsWk("Mc"))/CDbl(PvsWk("Mc"))%>" 
              size="12">
            </div></td>
          <td><div align="center"></div></td>
        </tr>
      </table></td>
  </tr>
</table>
</body>
</html>
<%
Wk.Close()
Set Wk = Nothing
%>
<%
PvsWk.Close()
Set PvsWk = Nothing
%>
<%
YTD.Close()
Set YTD = Nothing
%>
<%
PvsYTD.Close()
Set PvsYTD = Nothing
%>
 
Old March 20th, 2006, 06:39 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

I was looking for the problematic data, not the whole page. I get put of a post when the poster fails to read replies poperly. had you done this you would have addressed questions asked and posted the error.


Wind is your friend
Matt
 
Old March 21st, 2006, 08:32 AM
Authorized User
 
Join Date: Dec 2004
Location: Sunman, IN, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sorry for the miscommunication. I thought that I'd post all of the code in case the problematic section was a result of something earlier in the code.

Thanks for your help!

 
Old March 27th, 2006, 07:11 AM
Authorized User
 
Join Date: Mar 2006
Location: Aberdeen, Scotland, United Kingdom.
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Dear skwilliams,

That equation needs a bit of error checking because there is one very common value for which it WILL fail, i.e.

CDbl(PvsSunCount("Mc")) = 0

In EE this is what we call a singularity, or in other words, division by zero is not defined.

Not to mention if the values are not numeric...

Every time I need to show % differences I use something like this:

If B = 0 then
 e =(A - B)/(B + 1e-6)
else
 e = (A-B)/B
end if

This will give you a very high value for the % difference instead of an error.

You may what to do something different.

Bear in mind that (A-B)/B CAN be very different from (A-B)/A. The denominator should generally be the most "trusted" or sound value: You don't want to compare against a far-off value.

Another idea, if you always want to show a positive value:

ABS(A - B)/B

All the above code is loose pseudo-code. I trust you will take it to your language of choice.

Also: I guess it's a matter of style, but I would change that line with a call to a function that would do all the calcs and error checking for you, like so:

<%=pctDiff() %>


Cheers, Joel
 
Old March 27th, 2006, 03:12 PM
Authorized User
 
Join Date: Dec 2004
Location: Sunman, IN, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If I were writing this code (the IfThen statement)in ASP. Where would I put it? Within the tables cell or somewhere else and do a response.write to the table's cell.

You'll have to excuse me. I'm just an ASP beginner.

Thanks.

 
Old March 29th, 2006, 09:41 AM
Authorized User
 
Join Date: Mar 2006
Location: Aberdeen, Scotland, United Kingdom.
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You could do this:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Dim Wk
Dim Wk_numRows

Set Wk = Server.CreateObject("ADODB.Recordset")
Wk.ActiveConnection = MM_McPOrderCount_STRING
Wk.Source = "SELECT * FROM qryWeek_Crosstab"
Wk.CursorType = 0
Wk.CursorLocation = 2
Wk.LockType = 1
Wk.Open()

Wk_numRows = 0

Function pctDif(sItem)

Dim v1
Dim v2
Dim pctD

v1 = CDbl(SunCount(sItem))
v2 = CDbl(PVsSunCount(sItem))

If v2 = 0 then
  pctD = (v1-v2)/(v2 + 1e-6)
else
  pctD = (v1-v2)/v2
end if

pctDif = pctD

End Function

%>

-----

I have assumed that SunCount and PVsSunCount are objects defined at the Server-side top level (not within any function). If this is the case, the can be referenced from any function (they are Global).

Then, whenever you need to display the value


<% =pctDif("Mc") %>


----

The "=" sign is equivalent to Response.Write()

Hope this helps :)

Joel




Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate Percentage with Date mateenmohd Access 2 February 4th, 2008 06:26 AM
how to calculate the percentage in sql yvr238 SQL Server 2000 1 May 13th, 2006 03:28 AM
Urgent! how to calculate difference between dates Subuana Beginning VB 6 2 March 22nd, 2006 05:34 PM
percentage darkhalf Javascript 2 October 28th, 2005 02:55 PM
how to calculate difference in dates ashu_gupta75 Excel VBA 3 August 9th, 2004 06:11 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.