Calculate a difference percentage

skwilliams · March 17th, 2006, 12:33 PM

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?

adam_kahtava · March 18th, 2006, 01:15 AM

What is the error message?

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

- A.Kahtava

adam_kahtava · March 18th, 2006, 01:22 AM

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

mat41 · March 19th, 2006, 11:54 PM

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

skwilliams · March 20th, 2006, 10:03 AM

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
%>

mat41 · March 20th, 2006, 06:39 PM

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

skwilliams · March 21st, 2006, 08:32 AM

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!

joelchaconf · March 27th, 2006, 07:11 AM

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

skwilliams · March 27th, 2006, 03:12 PM

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.

joelchaconf · March 29th, 2006, 09:41 AM

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