Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.***
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])))
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])))
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.
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)
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.***
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])))
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])))