Navigate to a sheet based on sum condition

[Beardwil]

Hi. I want to be able to navigate to another sheet using a button based on the value of a sum formula (which is the value of the label on the same button). If the value is 0, I want to stay on the same sheet. The current sheet is 'PYMS Assessments'.  The expression I have created is not working and is as follows:

=dual if( Sum({<[PYMS Current Inpatient?] = {'Y'},[Rnk_Last_to_First] = {1}>}[Patients with PYMS of 2+ Counter]) = 0,'PYMS Assessments','PYMS Patient Level')

The pseudo code for what I want is:

if(sum of the [Patients with PYMS of 2+ Counter] field is zero (given the filter values), then navigate to the same sheet 'PYMS Assessments', otherwise go to sheet 'PYMS Patient Level'

If someone can help me with the syntax, that would be great. Thanks

[Or · Accepted solution]

Create a button, set the Navigation option to "Go to a sheet defined by the sheet ID", and then use a formula along the lines of:

if(Sum({<[PYMS Current Inpatient?] = {'Y'},[Rnk_Last_to_First] = {1}>}[Patients with PYMS of 2+ Counter]) = 0,'SheetIDToGoToHere','OtherSheetIDHere')

You could also use sheet names, but I personally prefer to use IDs.

This is basically your formula as it was, without the dual() that seems entirely out of place.

[Beardwil]

Great, thanks Or!