10 Replies Latest reply on Jul 26, 2018 5:09 AM by Mahfooj Khan

    Fix the “Cannot mix aggregate and non-aggregate arguments with this function” error when creating a calculated field

    Pedro Zampella

      I need to create a Dimension (calculated field) that depends on a filtered field.

      My dataset has four columns: ID (Int), Variable Name (String), Value (Int) and Client ID (String). For each client ID there will be a value for each type of variable.

      There are 6 types of Variable Name, and for each row there is a unique ID.

      I'm using a filter by Variable Name so there is one, and only one variable type available at the same time.

      In order to display it in a stacked bar chart by that dimension, I have created this calculation field (In reality, the statement has six IFs, but I'll use just one to illustrated it):

      IF ATTR([1 (ML_Output)].[Variable Name])= "Interest Rate" THEN [Interest Rate] END 

      That way, when the filter is in "Interest Rate", the ATTR function should show the only type of variable available in the Variable Name field, which would be "Interest Rate". The problem is that it shows the "Cannot mix aggregate and non-aggregate arguments with this function" error.

      Some solutions that I have already tried:

      1.- The original calculation that was doing what I wanted used a Parameter:

      IF [Parameter 1]="Interest Rate" THEN [Interest Rate] END 

      However, since parameters won't update automatically when the dataset changes, I can't use it anymore in production.

      2.- Aggregating the result of the statement would also avoid the error:

      IF ATTR([1 (ML_Output)].[Variable Name])= "Interest Rate" THEN ATTR([Interest Rate]) END

      The problem in this case is that the final result is a measure, and I need a dimension in order to create the stacked bar.

      Any other ideas?