variable sum across columns

Forum|Forum|19 years ago
February 14, 2007
6 replies
361 views

I've been trying to figure out how to automate this, and can't seem to work
it out. I've hodge-podged it before, requiring me to update the "current
month" every month, and I'm sure there's got to be a better (automatic) way
of doing this. I'm hoping someone can provide the missing pieces.

In an SQL table I have one record for each unique ID that provides last
year's monthly sales figures, and the current year's monthly sales figures
as they become available. It is possible for any of the sales value fields
(previous or current) to be zero (0):

[salesdata]
ID PrevJan PrevFeb PrevMar ... PrevDec CurrJan CurrFeb
CurrMar ... CurrDec

Each month I am given an updated version of this table which will include
the most recent "Curr" month's values.

This table underlies an asp (VBScript) page which reports this data. A
table on the page lists the monthly values (prev & curr), the percentage of
change monthly, and the year-to-date totals of all three (prev, curr,
percent of change).

JAN FEB MAR APR MAY JUN ...
DEC YTD
PREV 5 5 5 5 5
5 5 20
CURR 10 10 10 0 0
0 0 30
% CHANGE 200 200 200 200
150

** The year-to-date, of course, being the sums of all months UP TO THE MOST
CURRENT DATA uploaded into the table. **

The above example shows what I need to do. Note that:
1) The most current month's data is APR (not May as it seems). This ID had
no sales in CURR April.
2) The PREV YTD value sums only Jan-Apr, as does CURR YTD.

Here are my stumbling blocks:

1) What is the most efficient way to sum the COLUMNs across the row to gain
my YTD values, and still only grab the ones relevant for my current YTD? In
SQL? On my page?

2) How can I automate the YTD calculations of PREV to include ONLY the
"current" months (up to the currently imported data) ... allowing for the
fact that the latest month may have ZERO sales?

Again, I've hodge-podged this in the past by manually creating all the YTD
possibilities in my asp page, pulling a "lastest data" value from the
database, and using it in a CASE call to find which summing formulas to use
... but now that some other changes are forcing me to revisit this, I'm
looking for a more "correct" solution.

Thanks for your suggestions.

Server side applications

This topic has been closed for replies.

N

Newsgroup_UserAuthor

Inspiring

Somehow, the value got used for both sides of the equation. Should be:

<td width=10% align="left" valign="middle" class="txtfldgry">
<input <%If (CStr(Request.Form("displaytype")) = CStr("radio_M")) Then
Response.Write("checked=""checked""") : Response.Write("")%>
name="displaytype" type="radio" value="radio_M"
onChange="document.form1.submit()"> Monthly</td>
<td width=15% align="left" valign="middle" class="txtfldgry"> <input <%If
(CStr(Request.Form("displaytype")) = CStr("radio_Q")) Then
Response.Write("checked=""checked""") : Response.Write("")%>
name="displaytype" type="radio" value="radio_Q"
onChange="document.form1.submit()"> Quarterly </td>

N

Newsgroup_UserAuthor

Inspiring

...while I have your attention, are you good with radio buttons?

My form has a radio button group with 2 buttons, "Monthly" and "Quarterly".
I have applied an onchange event that submits the form and will change what
is displayed on the page to change. The default view is "Monthly". Problem
is, when I select "Quarterly", the page reloads, and I lose the "Quarterly"
setting and the monthly display comes up again.

Any idea how I can get my radio button selection to remain through the
submit? I'm obviously not very good with radio buttons, either. DW put
the IF statement in there, and honestly, I'm not sure how that's supposed to
work. Do you know?

<td width=10% align="left" valign="middle" class="txtfldgry">
<input <%If (CStr("radio_M") = CStr("radio_M")) Then
Response.Write("checked=""checked""") : Response.Write("")%>
name="displaytype" type="radio" value="radio_M"
onChange="document.form1.submit()">
Monthly</td>
<td width=15% align="left" valign="middle" class="txtfldgry">
<input <%If (CStr("radio_M") = CStr("radio_Q")) Then
Response.Write("checked=""checked""") : Response.Write("")%>
name="displaytype" type="radio" value="radio_Q"
onChange="document.form1.submit()">
Quarterly </td>

N

Newsgroup_UserAuthor

Inspiring

You are so awesome! Thanks so much for you help with this!

"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
news:er2lr1$l6u$1@forums.macromedia.com...
>> I do have another question about how to better automate this, with regard
>> to the "thisMonth" variable. You see, I don't always get my data in a
>> timely fashion - so we could be in June, and still only have data posted
>> through April. As it stands, I have to insert the month value of the
>> lastest available data into a table, and reference that to find the
>> "thisMonth" value. I'm wondering, however, if by running a query summing
>> the current months, I can then find the last month with values >0 ...
>> this would tell me what my "thisMonth" value is. Problem is ... while I
>> can dream up the concept, I'm not sure how to DO it in SQL. Is it
>> doable? Is there a way I can pull the month from a query like this to
>> use as the "thisMonth" variable you suggested below?
>
> Yes. Where and how depends on when you get the data.
> If you've already got the current year's data, you can find the last month
> inside your first loop.
>
> YTD = 0
> For X = 1 To 12
> If myRS.Fields(X).Value > 0 Then
> thisMonth = X
> End If
> YTD = YTD + myRS.Fields(X).Value
> Next
>
> Then you can use thisMonth to calculate the previous years' sales to that
> point as before using For X = 1 To thisMonth.
>

N

Newsgroup_UserAuthor

Inspiring

> I do have another question about how to better automate this, with regard
> to the "thisMonth" variable. You see, I don't always get my data in a
> timely fashion - so we could be in June, and still only have data posted
> through April. As it stands, I have to insert the month value of the
> lastest available data into a table, and reference that to find the
> "thisMonth" value. I'm wondering, however, if by running a query summing
> the current months, I can then find the last month with values >0 ... this
> would tell me what my "thisMonth" value is. Problem is ... while I can
> dream up the concept, I'm not sure how to DO it in SQL. Is it doable? Is
> there a way I can pull the month from a query like this to use as the
> "thisMonth" variable you suggested below?

Yes. Where and how depends on when you get the data.
If you've already got the current year's data, you can find the last month
inside your first loop.

YTD = 0
For X = 1 To 12
If myRS.Fields(X).Value > 0 Then
thisMonth = X
End If
YTD = YTD + myRS.Fields(X).Value
Next

Then you can use thisMonth to calculate the previous years' sales to that
point as before using For X = 1 To thisMonth.

N

Newsgroup_UserAuthor

Inspiring

Thanks! That is so much better than what I was doing before.

I do have another question about how to better automate this, with regard to
the "thisMonth" variable. You see, I don't always get my data in a timely
fashion - so we could be in June, and still only have data posted through
April. As it stands, I have to insert the month value of the lastest
available data into a table, and reference that to find the "thisMonth"
value. I'm wondering, however, if by running a query summing the current
months, I can then find the last month with values >0 ... this would tell me
what my "thisMonth" value is. Problem is ... while I can dream up the
concept, I'm not sure how to DO it in SQL. Is it doable? Is there a way I
can pull the month from a query like this to use as the "thisMonth" variable
you suggested below?

"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
news:er1n1q$duc$1@forums.macromedia.com...
> > 1) What is the most efficient way to sum the COLUMNs across the row to
> gain
>> my YTD values, and still only grab the ones relevant for my current YTD?
>> In SQL? On my page?
>
> Generally, databases shouldn't be used for mathematical calculations that
> don't involve aggregating the data somehow, but for a simple addition, you
> could do it either way. Because the columns being added together change,
> I would suggest doing this in your ASP page. You can set a month variable
> (thisMonth = Month(Date())) and use that to access your sales figures. A
> recordset can be accessed by index or by key, so if your recordset columns
> are [Jan, Feb, Mar, ..., Dec], then you can add together easily:
> <%
> YTD = 0
> For X = 1 To ThisMonth
> YTD = YTD + myRS.Fields.Item(X-1).Value
> Next
> %>
>
> Why (X-1)? Because the recordset counts from 0. If you have other
> columns in the recordset, then just adjust the index accordingly. For
> example [ID, Jan, Feb, ..., Dec] would use just (X) to reference the right
> month since Jan = Month 1 = Column(1) in the recordset.
>
> If you have NULL values, you have to test for them first, because anything
> + NULL = NULL. The isNull() function will help with that.
>
>> 2) How can I automate the YTD calculations of PREV to include ONLY the
>> "current" months (up to the currently imported data) ... allowing for the
>> fact that the latest month may have ZERO sales?
>
> I actually answered this with the first question. You can get the current
> month and then use that month number as the max value for a FOR loop that
> adds each month in turn until the current month has been reached.
>

N

Newsgroup_UserAuthor

Inspiring

> 1) What is the most efficient way to sum the COLUMNs across the row to
gain
> my YTD values, and still only grab the ones relevant for my current YTD?
> In SQL? On my page?

Generally, databases shouldn't be used for mathematical calculations that
don't involve aggregating the data somehow, but for a simple addition, you
could do it either way. Because the columns being added together change, I
would suggest doing this in your ASP page. You can set a month variable
(thisMonth = Month(Date())) and use that to access your sales figures. A
recordset can be accessed by index or by key, so if your recordset columns
are [Jan, Feb, Mar, ..., Dec], then you can add together easily:
<%
YTD = 0
For X = 1 To ThisMonth
YTD = YTD + myRS.Fields.Item(X-1).Value
Next
%>

Why (X-1)? Because the recordset counts from 0. If you have other columns
in the recordset, then just adjust the index accordingly. For example [ID,
Jan, Feb, ..., Dec] would use just (X) to reference the right month since
Jan = Month 1 = Column(1) in the recordset.

If you have NULL values, you have to test for them first, because anything +
NULL = NULL. The isNull() function will help with that.

> 2) How can I automate the YTD calculations of PREV to include ONLY the
> "current" months (up to the currently imported data) ... allowing for the
> fact that the latest month may have ZERO sales?

I actually answered this with the first question. You can get the current
month and then use that month number as the max value for a FOR loop that
adds each month in turn until the current month has been reached.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded