topic Re: creating an expression to sum two values together and return results greater than 1 only in App Development

creating an expression to sum two values together and return results greater than 1 only

Mike75 — Thu, 06 Jun 2024 05:40:10 GMT

Hi guys,

I'm trying to create an expression to sum two values together and return results greater than 1

My expression works but it also returns null or missing values and suppressing them on the presentation tab doesn't work

=if(sum([Overtime FTE])+sum([FTE])>1,sum([Overtime FTE])+sum([FTE]))

My report also has 4 dimensions: Employee Code, Pay Period, Award Category and Hospital.

The report is set up as a pivot table. It works ok as a straight table but I need to pivot by Pay Period.

Any help would be greatly appreciated!

Cheers,

Mike

Re: creating an expression to sum two values together and return results greater than 1 only

Sayed_Mannan — Thu, 06 Jun 2024 09:44:40 GMT

Hi, try this

=if(Sum({<PayPeriod>} [Overtime FTE] + [FTE]) > 1, Sum({<PayPeriod>} [Overtime FTE] + [FTE]))

here I use {<PayPeriod>} to apply the sum only within the selected pay period.

Re: creating an expression to sum two values together and return results greater than 1 only

marcus_sommer — Thu, 06 Jun 2024 12:23:51 GMT

As far as the expression returned any valid result in regard to the vertical and horizontal dimension-values they will be displayed.

Beside this - by using +- operators you will get NULL as result as far as any of the parts in NULL or not numeric. It may the intended behaviour in your case but if not you could avoid it by using something like:

rangesum(Field1, Field2)

Re: creating an expression to sum two values together and return results greater than 1 only

TauseefKhan — Thu, 06 Jun 2024 12:37:56 GMT

Hi @Mike75,

Check this expression :

=If(Not IsNull(sum([Overtime FTE])) and Not IsNull(sum([FTE])) and sum([Overtime FTE]) + sum([FTE]) > 1, sum([Overtime FTE]) + sum([FTE]))

***Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.***

Re: creating an expression to sum two values together and return results greater than 1 only

Mike75 — Thu, 06 Jun 2024 13:08:44 GMT

Thanks very much for your help Sayed! I also just had to add rangesum instead of adding the fields together:

=if(sum({<[PayPeriodFinYear]>}rangesum([Overtime FTE],[FTE]))>1.5,sum({<[PayPeriodFinYear]>}rangesum([Overtime FTE],[FTE])))

Re: creating an expression to sum two values together and return results greater than 1 only

Mike75 — Thu, 06 Jun 2024 13:15:30 GMT

Thanks Tauseef! I'll try your method out as well but I reckon I'll need rangesum in there as in the solution below:

=if(sum({<[PayPeriodFinYear]>}rangesum([Overtime FTE],[FTE]))>1.5,sum({<[PayPeriodFinYear]>}rangesum([Overtime FTE],[FTE])))

Re: creating an expression to sum two values together and return results greater than 1 only

Mike75 — Thu, 06 Jun 2024 13:21:50 GMT

Thanks Marcus adding rangesum and the pay period dimension into the expression did the trick:

=if(sum({<[PayPeriodFinYear]>}rangesum([Overtime FTE],[FTE]))>1.5,sum({<[PayPeriodFinYear]>}rangesum([Overtime FTE],[FTE])))