Alpha Video Training
Results 1 to 25 of 25

Thread: Total function confusion

  1. #1
    Member
    Real Name
    John Gamble
    Join Date
    Aug 2016
    Posts
    16

    Default Total function confusion

    I've been away from Alpha for a number of years and had to re-register using a new name.
    The function below works fine to total "KWH" if "Hour" = "01-02" or "02-03".
    As soon as the next "Hour" {03-04} is encountered the total zeros.
    I believe that the false result "0" in RED is the culprit but I cannot find a null function.
    The help function indicates there is no way to identify a numeric null.

    if(Hourly->Hour = "01-02" .or. Hourly->Hour = "02-03",total(Hourly->Kwh,GRP->Group 1),0)

    Any suggestions

  2. #2
    "Certified" Alphaholic CharlesParker's Avatar
    Real Name
    Charles Parker
    Join Date
    Dec 2012
    Location
    New Orleans, LA
    Posts
    2,115

    Default Re: Total function confusion

    Looks like you have a statement that says simply if the hour = X or Y then hour = a formulas value, otherwise it equals ZERO and my guess is the zero's are simply the display format. The comma with the RED zero in tow - isn't that specifically stating that anything else is ZERO?

    Please also understand, that I am a total NOOB - so if my questions/comments are out of whack from what's really going on here kindly disregard...I tried.
    NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

  3. #3
    Member
    Real Name
    John Gamble
    Join Date
    Aug 2016
    Posts
    16

    Default Re: Total function confusion

    Quote Originally Posted by CharlesParker View Post
    Looks like you have a statement that says simply if the hour = X or Y then hour = a formulas value, otherwise it equals ZERO and my guess is the zero's are simply the display format. The comma with the RED zero in tow - isn't that specifically stating that anything else is ZERO?

    Please also understand, that I am a total NOOB - so if my questions/comments are out of whack from what's really going on here kindly disregard...I tried.
    The hours "01-02, 02-03...24-01 represent a 24 hour day. I simply want to total selected periods while ignoring the rest. The problem is that as soon as it encounters an ignored value it zeros the total.
    e.g.
    Hour KWH
    --------------
    01-02 2.0
    02-03 3.0 Total-5.0 OK {If there are only these two records}

    01-02 2.0
    02-03 3.0
    03-04 1.0 Total=0.0 Should be 5.0

    Thanks John

  4. #4
    "Certified" Alphaholic CharlesParker's Avatar
    Real Name
    Charles Parker
    Join Date
    Dec 2012
    Location
    New Orleans, LA
    Posts
    2,115

    Default Re: Total function confusion

    It sure seems like you do not need an IF statement to ADD things. To me the IF statement means that there's an ELSE statement by default adn your default ELSE is "0"
    what happens if you add 07-08?
    If I am right its ZERO

    why not just add the values in a simple A+B statement or a summary value calculation? I think what would really be necessary is to say if this is a grid, UX, repeating section, list control, etc. because at this point it seems to me that all your trying to do is add up the values as they are entered. What are you working with?
    Again, the if statement is doing EXACTLY what your asking it to, IF - ELSE as per the value following the comma (ZERO)

    If anyone else sees this different - post up!
    NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

  5. #5
    "Certified" Alphaholic CharlesParker's Avatar
    Real Name
    Charles Parker
    Join Date
    Dec 2012
    Location
    New Orleans, LA
    Posts
    2,115

    Default Re: Total function confusion

    OH - also this is the desktop area and I am outside of my area of Alpha usage! I am much more familiar with the webside - but regardless I still see that function as doing exactly what your telling it to!
    NWCOPRO: Nuisance Wildlife Control Software My Application: http://www.nwcopro.com "Without forgetting, we would have no memory at all...now what was I saying?"

  6. #6
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Total function confusion

    You are using this on a report? Otherwise GRP->Group 1 likely has no meaning.

    total(Hourly->Kwh,GRP->Group 1) does that. It doesn't care what your if statement wrapped around it does. Total() doesn't do filters.

    You probably need a tablesum() function something like

    tablesum("hourly","Hour = '01-02' .or. Hour = '02-03'","kwh")

    You could also define a calculated field for the report

    if(Hourly->Hour = "01-02" .or. Hourly->Hour = "02-03",kwh,0)

    and total that calculated field.
    There can be only one.

  7. #7
    Member
    Real Name
    John Gamble
    Join Date
    Aug 2016
    Posts
    16

    Default Re: Total function confusion

    Charles.

    Right you are. I'm trying to do the wrong thing.

    Stan

    You got it. I've been away from Alpha too long.
    Gotta get my head around it once again.

    Appreciate, John

  8. #8
    Member
    Real Name
    John Gamble
    Join Date
    Aug 2016
    Posts
    16

    Default Re: Total function confusion

    Well one down, one to go.

    I am working within a form, based on a set.

    Meter
    ..."==Daily {One record per day, per meter}
    ......"==Hourly {24 records per day, one per hour}

    As I am entering the 24 hourly records, the selected cumulative KW Hour totals are displayed on the form .{e.g. 9AM to 5PM = C_KWH917} and the total KW Hours {T_KWH}.

    Now the $64,000 question. How do I transfer these totals one level up to the appropriate DAILY record?

    Suggestions appreciated.

    PS. this used to be a cinch in A4 as it was set based.

  9. #9
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Total function confusion

    You could have a posting field rule.
    You could have a posting operation run the next day..
    The daily fields could be calculated with a tablesum().
    Last edited by Stan Mathews; 08-17-2016 at 08:17 AM.
    There can be only one.

  10. #10
    Member
    Real Name
    John Gamble
    Join Date
    Aug 2016
    Posts
    16

    Default Re: Total function confusion

    Quote Originally Posted by Stan Mathews View Post
    You could have a posting field rule.
    You could have a posting operation run the next day..
    The daily fields could be calculated with a tablesum().
    Good morning Stan

    1. Tried posting rule but
    .......IF choosing "ADD", then corrections add again and totals are too high
    .......If choosing "REPLACE" then only this record is recorded.

    2. Same issue if changes made later (Also see 3.)

    3. Tried tablesum() but could not figure out the correct filter {variable?} to restrict it to the current DAY only.
    I can re-specify all the filter details but was trying to tablesum() the previously calculated totaled HRS.

    Thanks for replying.
    John

  11. #11
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Total function confusion

    To restrict the tablesum() to the desired day there must be a day field in the hourly table.

    Meter
    ..."==Daily {One record per day, per meter}
    ......"==Hourly {24 records per day, one per hour}
    Probably need a sample database/set to see the fields and set linkages/linking fields..
    There can be only one.

  12. #12
    Member
    Real Name
    John Gamble
    Join Date
    Aug 2016
    Posts
    16

    Default Re: Total function confusion

    Stan

    There are matching dates in the HOURLY and DAILY records.

    I've tried tablesum("Hourly","?????????","T_KWH0917") Where ?????? has been various forms of dates. I just can't nail it.

    I'll try to put together a sample DB

    Thanks John

  13. #13
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Total function confusion

    1. Tried posting rule but
    .......IF choosing "ADD", then corrections add again and totals are too high
    .......If choosing "REPLACE" then only this record is recorded.
    Using a posting field rule you don't make corrections, only adjustments (+/-) or you make a correcting entry of the original amount negative and then a corrected entry. ADD is the correct choice.
    There can be only one.

  14. #14
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Total function confusion

    tablesum("Hourly","date_field_name =" +s_quote(date_variable_name) ,"T_KWH0917")

    but you need to add the meter designator to that as well so the sample is still needed.

    Just a reminder since you've been away from Alpha for a while. Calculated fields are recalculated only when a value in the table where the calculation if defined changes, which will never happen here because the values changing are in another table. You will need to use one of the force recalculation options.

    A5_RECALC_CALC_FIELDS()
    <TBL>.RECALC_CALCFIELDS()

    or

    Right click on a table in the Control Panel, and select Utilities > Recalculate Calc Fields (Field Rules).

    Or, click Table > Utilities > Recalculate Calc Fields (Field Rules).

    Click OK.
    Last edited by Stan Mathews; 08-17-2016 at 09:45 AM.
    There can be only one.

  15. #15
    Member
    Real Name
    John Gamble
    Join Date
    Aug 2016
    Posts
    16

    Default Re: Total function confusion

    Working on a sample. Been a while since I had to do this.
    I'll have to figure it out.
    Thank, John

  16. #16
    Member
    Real Name
    John Gamble
    Join Date
    Aug 2016
    Posts
    16

    Default Re: Total function confusion

    Stan
    Sample DB attached
    Thanks John
    Attached Files Attached Files

  17. #17
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,609

    Default Re: Total function confusion

    John

    What build of Alpha are you using?
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  18. #18
    Member
    Real Name
    John Gamble
    Join Date
    Aug 2016
    Posts
    16

    Default Re: Total function confusion

    V10 2562

  19. #19
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,609

    Default Re: Total function confusion

    Quote Originally Posted by unique.machining View Post
    V10 2562
    That's pretty old.

    I'm using 4369/3712.

    http://downloads.alphasoftware.com/A...chDownload.ASP
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  20. #20
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Total function confusion

    On your form you could have a calculated field with the expression

    tablesum("hourly","meter_no = "+quote(Daily->Meter_No) + " .and. date = "+s_quote(daily->date),"C_K07_19")

    to total the C_K07_19 for the visible meter on the selected date.

    in a calculated field rule for a field in the daily table you don't need the Daily->

    tablesum("hourly","meter_no = "+quote(Meter_No) + " .and. date = "+s_quote(date),"C_K07_19")
    There can be only one.

  21. #21
    Member
    Real Name
    John Gamble
    Join Date
    Aug 2016
    Posts
    16

    Default Re: Total function confusion

    Al

    As am I. Didn't bother updating as most changes appear to be web related. Will do now.

    John

  22. #22
    Member
    Real Name
    John Gamble
    Join Date
    Aug 2016
    Posts
    16

    Default Re: Total function confusion

    Stan

    Have to leave for a couple of hours, but will try as soon as I return.

    Appreciate the time you have spent.

    John

  23. #23
    Member
    Real Name
    John Gamble
    Join Date
    Aug 2016
    Posts
    16

    Default Re: Total function confusion

    Stan

    I never would have figured out that filter without your assistance. It worked right out of the box.

    Thank you ever so much.

    John

  24. #24
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Total function confusion

    To be consistent

    tablesum("hourly","meter_no = "+s_quote(Meter_No) + " .and. date = "+s_quote(date),"C_K07_19")

    is equivalent and might make things easier to understand later.

    S_quote() transforms a variable into character format with suitable modifications for the data type.
    There can be only one.

  25. #25
    Member
    Real Name
    John Gamble
    Join Date
    Aug 2016
    Posts
    16

    Default Re: Total function confusion

    Stan

    Tis clearer. The filter was driving me crazy.

    You are a gentleman & scholar, as far as a judge of fine wine well...

    John

Similar Threads

  1. Total() function in a form???
    By Gustavo1478 in forum Windows Desktop Applications
    Replies: 24
    Last Post: 03-28-2014, 08:56 AM
  2. Total function using data from set
    By DougMcLean in forum Alpha Five Version 5
    Replies: 4
    Last Post: 04-22-2004, 07:12 AM
  3. Total () Function
    By Martin Konieczny in forum Alpha Five Version 5
    Replies: 3
    Last Post: 07-28-2003, 07:06 AM
  4. odd total function required
    By eeetee in forum Alpha Five Version 4
    Replies: 4
    Last Post: 11-21-2002, 07:03 AM
  5. TOTAL function
    By David Patterson in forum Alpha Five Version 4
    Replies: 2
    Last Post: 06-20-2001, 08:09 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •