3 Replies Latest reply on Jan 23, 2019 7:54 AM by Michel Caissie

    Problem with if statement and mixing aggregate/non-aggregate values

    Brandon Scherzer

      In our database, we have a fiscal year GUID in our invoice table that represents the fiscal year. We also have a fiscal year table that identifies the fiscal year description (ie. 2016, 2017, 2018, etc.) that represents the GUID that is used as a foreign key in the invoice table.

       

      A fiscal year GUID only gets assigned a year description when the fiscal year is closed, so all invoices in the current fiscal year have a null value in the fiscal year GUID column. To get an actual fiscal year description on all invoice, I'm creating a calculated field that is something like this:

      IF [FiscalYearGUID] = NULL

      THEN MAX([FiscalYearDescription]) + 1

      ELSE [FiscalYearDescription)

      END

       

      This gives me the cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions error.

       

      When I try something like this, I get the fiscal year descriptions for the closed fiscal years, but still get nulls for those invoices that have a null Fiscal Year GUID:

      IF ISNULL(attr([FiscalYear]))

      THEN max([YearName]) + 1

      ELSE (attr([YearName]))

      END

       

      Does anyone have any ideas on how to resolve this?

       

      Thanks!