Sum on Formula That's Summed? 2

[supportsvc]

I am unable to do a Sum on the following formula

Q1Taxable:

If Sum ({@Q1}, {@Name}) > {@UILimit} then {@UILimit} else Sum ({@Q1}, {@Name})

Where Q1:

If {PR_EmployeeTaxHistory.CalendarMonth} IN (["01", "02", "03"]) then {PR_EmployeeTaxHistory.GrossWages} else 0

The Q1Taxable is on the Group's Header (Group is the Employee Name)
Name is (lastname, firstname)

When attempting to create another Formula to Sum(@Q1Taxalbe), I get the following error
"This field cannot be summarized"

Since it's already summarized. How do I or what do I need to do so I can sum the Q1Taxable formulated field?

 

[IanWaterman]

You will have to use a Variable.
Assuming you want to sum on Employee
Create 3 formula
@reset // place this in Employee group header and suppress
Whileprintingrecords;
global NumberVar Q1Tax:=0;

@Eval// place this in Q1Taxable group header and suppress
Whileprintingrecords;
global NumberVar Q1Tax;

Q1Tax:= Q1Tax + (If Sum ({@Q1}, {@Name}) > {@UILimit} then {@UILimit} else Sum ({@Q1}, {@Name}));

@display // Place this in Employee group footer
Whileprintingrecords;
global NumberVar Q1Tax;

Ian

 

[supportsvc]

Thank you!
Will try it.

Though there is only one Group and on Employee

 

[supportsvc]

I am getting a "a number is required here" when trying to save the @Eval
on

Q1Tax + (If Sum ({@Q1}, {@Name}) > {@UILimit} then {@UILimit} else Sum ({@Q1}, {@Name}))

Also, there isn't a Group on Q1Taxable and attempting, it doesn't show as an option

Here's the Design View with the Formula fields, etc ...

Looking for a Report total from what I have working but can't do a Sum on the
1) Q1Taxable =

If Sum ({@Q1}, {@Name}) > {@UILimit} then {@UILimit} else Sum ({@Q1}, {@Name})

2) Q2Taxable =

if Sum ({@Q1}, {@Name}) + Sum ({@Q2}, {@Name})> {@UILimit} then {@UILimit}-{@Q1Taxable} else Sum ({@Q2}, {@Name})

3) Q3Taxable =

if Sum ({@Q1}, {@Name})+Sum ({@Q2}, {@Name})+Sum ({@Q3}, {@Name}) > {@UILimit} then {@UILimit} - {@Q1Taxable}-{@Q2Taxable} else Sum ({@Q3}, {@Name})

4) Q4Taxable =

if Sum ({@Q1}, {@Name})+Sum ({@Q2}, {@Name})+Sum ({@Q3}, {@Name})+Sum ({@Q4}, {@Name}) > {@UILimit} then {@UILimit} - {@Q1Taxable}-{@Q2Taxable}-{@Q3Taxable} else Sum ({@Q4}, {@Name})

The Green are the above and need Report Totals
The Purple on the end is the formula for TaxableTotal (which is the total of the 4 quarters) and need that to total on Report as well

 

[IanWaterman]

Why do you want to Group on Q1Taxable?

I assumed this formula returned a number
If Sum ({@Q1}, {@Name}) > {@UILimit} then {@UILimit} else Sum ({@Q1}, {@Name})

Did you follow my syntax exactly? The ; and : are important

Whileprintingrecords;
global NumberVar Q1Tax;

Q1Tax:= Q1Tax + (If Sum ({@Q1}, {@Name}) > {@UILimit} then {@UILimit} else Sum ({@Q1}, {@Name}));

Ian

 

[lbass]

It could be a currency, in which case the variable should be currencyvar, not numbervar.

-LB

 

[supportsvc]

IanWaterman,

it said

@Eval// place this in Q1Taxable group header and suppress
Whileprintingrecords;
global NumberVar Q1Tax;

Yes it does. It returns the results in Green from the screen shot I shared.

I copied and pasted exactly what you have provided

 

[supportsvc]

Ibass,
It's a number but formatted to show as $

 

[IanWaterman]

Misread your first post should be in Group Employee Name

Ian

 

[supportsvc]

I have both the @Reset and @Eval on the Employee group
and @Display on the Report Footer and results in $0

 

[lbass]

Since you auppear to only want report totals, revmove the reset formula.

This is the entire evaluation formula for the {@name group header) (suppress the formula):

//{eval}:
Whileprintingrecords;
NumberVar Q1Tax;
NumberVar Q2Tax;
NumberVar Q3Tax;
NumberVar Q4Tax;
Q1Tax:= Q1Tax + {@Q1taxable};
Q2Tax:= Q2Tax + {@Q2taxable};
Q3Tax:= Q3Tax + {@Q3taxable};
Q4Tax:= Q4Tax + {@Q3taxable};

In the report footer, use 4 separate formulas for each, like this:
Whileprintingrecords;
NumberVar Q1Tax;

For the purple row total, use a formula:
{@Q1taxable}+{@Q2taxable}+{@Q3taxable}+{@Q4taxable};//your original formulas for these

For the grand total, use:

Whileprintingrecords;
NumberVar Q1Tax;
NumberVar Q2Tax;
NumberVar Q3Tax;
NumberVar Q4Tax;
Q1Tax+Q2Tax+Q3Tax+Q4Tax

-LB

 

[supportsvc]

Hello Ibass,
Thank you

I am getting "a number is required here" at

Q1Tax + {@Q1taxable};

Also, not sure what the Q1Tax - Q4Tax fields are?

the only taxes on Employee is Q1Taxable - Q4Taxable

NOTE:
Q1Taxable =

If Sum ({@Q1}, {@Name}) > {@UILimit} then {@UILimit} else Sum ({@Q1}, {@Name})

Q1 =

If {PR_EmployeeTaxHistory.CalendarMonth} IN (["01", "02", "03"]) then {PR_EmployeeTaxHistory.GrossWages} else 0

 

[IanWaterman]

NumberVar Q1Tax;
NumberVar Q2Tax;
NumberVar Q3Tax;
NumberVar Q4Tax;

Are variables created to capture your tax amounts and effectively produce a running total in the background.
If you do not suppress the formula you will see the Q4Tax value increment as you run through data.

You can call them what you like you just need to be consistent. However, LBs logic is spot on and I would stick to her suggestion.

The error you are getting implies that {@Q1taxable} is not a number, which does not not seem likely
Make sure you are using : and ; as detailed.

Ian

 

[lbass]

When you show formulas, you should include all parts of it. If you didn’t include the “whileprintingrecords; numbervar Q1tax” in your formula you would get an error because the formula editor wouldn’t recognize what Q1tax is.

-LB

 

[supportsvc]

Hello,
This is what I have

//@Eval
Whileprintingrecords;
NumberVar Q1Tax;
NumberVar Q2Tax;
NumberVar Q3Tax;
NumberVar Q4Tax;
Q1Tax:= Q1Tax + {@Q1taxable};
Q2Tax:= Q2Tax + {@Q2taxable};
Q3Tax:= Q3Tax + {@Q3taxable};
Q4Tax:= Q4Tax + {@Q3taxable};

It's on the Employee Group and the field is Suppressed

@Q1Taxable returns a numeric value as you can see in the example (Green font color fields)

https://files.engineering.com/getfi...-45f2-bf81-eb04e0fef0e4&file=FormulaError.JPG

 

[lbass]

It might be numeric, but that doesn’t mean it is a number. Run the cursor over the {@Q1taxable} formula or right click on it->browse field to see what the actual datatype is and then report back.

-LB

 

[supportsvc]

It's a floating currency

 

[lbass]

Just change all references to “numbervar” to “currencyvar” in all formulas. You might get some error messages until all formulas are changed to currencyvar.

-LB

 

[supportsvc]

Got it

So no errors but still displaying $0 for the Total on Employee for the Green and $12600 for the Purple

 

[supportsvc]

It's the report