# Total function confusion

• 08-15-2016, 11:42 PM
unique.machining
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
• 08-16-2016, 12:01 AM
CharlesParker
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. :grin:
• 08-16-2016, 12:24 AM
unique.machining
Re: Total function confusion
Quote:

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. :grin:

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
• 08-16-2016, 12:42 AM
CharlesParker
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!
• 08-16-2016, 12:44 AM
CharlesParker
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!
• 08-16-2016, 08:27 AM
Stan Mathews
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.
• 08-16-2016, 08:38 AM
unique.machining
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
• 08-16-2016, 10:45 PM
unique.machining
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.
• 08-17-2016, 07:53 AM
Stan Mathews
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().
• 08-17-2016, 09:07 AM
unique.machining
Re: Total function confusion
Quote:

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.

John
• 08-17-2016, 09:18 AM
Stan Mathews
Re: Total function confusion
To restrict the tablesum() to the desired day there must be a day field in the hourly table.

Quote:

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..
• 08-17-2016, 09:32 AM
unique.machining
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
• 08-17-2016, 09:33 AM
Stan Mathews
Re: Total function confusion
Quote:

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.
• 08-17-2016, 09:39 AM
Stan Mathews
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.
• 08-17-2016, 09:42 AM
unique.machining
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
• 08-17-2016, 10:23 AM
unique.machining
Re: Total function confusion
Stan
Sample DB attached
Thanks John
• 08-17-2016, 10:38 AM
Al Buchholz
Re: Total function confusion
John

What build of Alpha are you using?
• 08-17-2016, 10:42 AM
unique.machining
Re: Total function confusion
V10 2562
• 08-17-2016, 10:50 AM
Al Buchholz
Re: Total function confusion
Quote:

Originally Posted by unique.machining
V10 2562

That's pretty old.

I'm using 4369/3712.

• 08-17-2016, 11:04 AM
Stan Mathews
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")
• 08-17-2016, 11:06 AM
unique.machining
Re: Total function confusion
Al

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

John
• 08-17-2016, 11:20 AM
unique.machining
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
• 08-17-2016, 09:21 PM
unique.machining
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
• 08-17-2016, 09:53 PM
Stan Mathews
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.
• 08-17-2016, 10:16 PM
unique.machining
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