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

[Mike75]

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

[Sayed_Mannan]

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.

[marcus_sommer]

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)

[TauseefKhan · Accepted solution]

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.***

[Mike75 · Accepted solution]

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])))

[Mike75 · Accepted solution]

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]))) 

[Mike75 · Accepted solution]

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])))