# Thread: Total function confusion

1. ## 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. ## 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.

3. ## Re: Total function confusion

Originally Posted by CharlesParker
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. ## 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!

5. ## 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!

6. ## 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.

7. ## 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. ## 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. ## 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().

10. ## Re: Total function confusion

Originally Posted by Stan Mathews
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. ## 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..

12. ## 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. ## 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.

14. ## 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.

15. ## 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. ## Re: Total function confusion

Stan
Sample DB attached
Thanks John

17. ## Re: Total function confusion

John

What build of Alpha are you using?

V10 2562

19. ## Re: Total function confusion

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

I'm using 4369/3712.

http://downloads.alphasoftware.com/A...chDownload.ASP

20. ## 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")

21. ## 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. ## 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. ## 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. ## 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.

25. ## 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

#### Posting Permissions

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