Resolve aggregate and Non-aggregate mix error for IF results in Tableau

Question

I am trying to do the following in Tableau.

If the chosen key figure is sales or volume (non-aggregate fields) then call calculation1 else calculation 2.

IF ATTR([Choose Key Figure])= "Sales"

THEN [Periodic Calculation]

ELSEIF ATTR([Choose Key Figure])= "Volume"

THEN [Periodic Calculation]

ELSEIF ATTR([Choose Key Figure])="profit"

THEN [Periodic Calculation 2]

ELSEIF ATTR([Choose Key Figure])="price per unit"

THEN [Periodic Calculation 2]

END 

But I am getting the following error: "Cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions."

Periodic Calculation

IF [Choose Vol/NNS]="NNS"

AND [Choose Period]=-1

AND DATEDIFF("month",[Dates], [Choose Current Month])<2

AND

DATEDIFF("month",[Dates],[Choose Current Month] )>0

then [(Actuals+Planned) NNS]

ELSEIF [Choose Vol/NNS]="Volume"

AND    [Choose Period]=-1

AND DATEDIFF("month",[Dates], [Choose Current Month])<2

AND

DATEDIFF("month",[Dates],[Choose Current Month] )>0

THEN [(Actuals + Planned) Volume]

ELSEIF [Choose Vol/NNS]="NNS"

AND [Choose Period]=-3

AND DATEDIFF("month",[Dates], [Choose Current Month])<4

AND

DATEDIFF("month",[Dates],[Choose Current Month] )>0

then [(Actuals+Planned) NNS]

ELSEIF [Choose Vol/NNS]="Volume"

AND [Choose Period]=-3

AND DATEDIFF("month",[Dates], [Choose Current Month])<4

AND

DATEDIFF("month",[Dates],[Choose Current Month] )>0

THEN [(Actuals + Planned) Volume]

ELSEIF [Choose Vol/NNS]="NNS"

AND [Choose Period]=-6

AND DATEDIFF("month",[Dates], [Choose Current Month])<7

AND

DATEDIFF("month",[Dates],[Choose Current Month] )>0

then [(Actuals+Planned) NNS]

ELSEIF [Choose Vol/NNS]="Volume"

AND     [Choose Period]=-6

AND DATEDIFF("month",[Dates], [Choose Current Month])<7

AND

DATEDIFF("month",[Dates],[Choose Current Month] )>0

THEN [(Actuals + Planned) Volume]

ELSEIF [Choose Vol/NNS]="NNS"

AND     [Choose Period]=1

AND DATEDIFF("month",[Dates], [Choose Current Month])<0

AND

DATEDIFF("month",[Dates],[Choose Current Month] )>-2

THEN [(Actuals+Planned) NNS]

ELSEIF [Choose Vol/NNS]="Volume"

AND     [Choose Period]=1

AND DATEDIFF("month",[Dates], [Choose Current Month])<0

AND

DATEDIFF("month",[Dates],[Choose Current Month] )>-2

THEN [(Actuals + Planned) Volume]

ELSEIF [Choose Vol/NNS]="NNS"

AND     [Choose Period]=3

AND DATEDIFF("month",[Dates], [Choose Current Month])<0

AND

DATEDIFF("month",[Dates],[Choose Current Month] )>-4

THEN [(Actuals+Planned) NNS]

ELSEIF [Choose Vol/NNS]="Volume"

AND     [Choose Period]=3

AND DATEDIFF("month",[Dates], [Choose Current Month])<0

AND

DATEDIFF("month",[Dates],[Choose Current Month] )>-4

THEN [(Actuals + Planned) Volume]

ELSEIF [Choose Vol/NNS]="NNS"

AND     [Choose Period]=6

AND DATEDIFF("month",[Dates], [Choose Current Month])<0

AND

DATEDIFF("month",[Dates],[Choose Current Month] )>-7

THEN [(Actuals+Planned) NNS]

ELSEIF [Choose Vol/NNS]="Volume"

AND     [Choose Period]=6

AND DATEDIFF("month",[Dates], [Choose Current Month])<0

AND

DATEDIFF("month",[Dates],[Choose Current Month] )>-7

THEN [(Actuals + Planned) Volume] 

END

Periodic Calculation 2

IF [Choose IGM$/NNSP]="NNSP"

AND [Choose Period]=-1

AND ATTR(DATEDIFF("month",[Dates], [Choose Current Month]))<2

and ATTR(DATEDIFF("month",[Dates],[Choose Current Month] ))>0

THEN [(Actuals + Planned) NNSP]

ELSEIF [Choose IGM$/NNSP]="IGM$"

AND [Choose Period]=-1

AND ATTR(DATEDIFF("month",[Dates], [Choose Current Month]))<2

and ATTR(DATEDIFF("month",[Dates],[Choose Current Month] ))>0

THEN [(Actuals + Planned) IGM$]

ELSEIF [Choose IGM$/NNSP]="NNSP"

AND [Choose Period]=-3

AND ATTR(DATEDIFF("month",[Dates], [Choose Current Month]))<4

and ATTR(DATEDIFF("month",[Dates],[Choose Current Month] ))>0

THEN [(Actuals + Planned) NNSP]

ELSEIF [Choose IGM$/NNSP]="IGM$"

AND [Choose Period]=-3

AND ATTR(DATEDIFF("month",[Dates], [Choose Current Month]))<4

and ATTR(DATEDIFF("month",[Dates],[Choose Current Month] ))>0

THEN [(Actuals + Planned) IGM$]

ELSEIF [Choose IGM$/NNSP]="NNSP"

AND [Choose Period]=-6

AND ATTR(DATEDIFF("month",[Dates], [Choose Current Month]))<7

and ATTR(DATEDIFF("month",[Dates],[Choose Current Month] ))>0

THEN [(Actuals + Planned) NNSP]

ELSEIF [Choose IGM$/NNSP]="IGM$"

AND [Choose Period]=-6

AND ATTR(DATEDIFF("month",[Dates], [Choose Current Month]))<7

and ATTR(DATEDIFF("month",[Dates],[Choose Current Month] ))>0

THEN [(Actuals + Planned) IGM$]

ELSEIF [Choose IGM$/NNSP]="NNSP"

AND [Choose Period]=1

AND ATTR(DATEDIFF("month",[Dates], [Choose Current Month]))<0

and ATTR(DATEDIFF("month",[Dates],[Choose Current Month] ))>-2

THEN [(Actuals + Planned) NNSP]

ELSEIF [Choose IGM$/NNSP]="IGM$"

AND [Choose Period]=1

AND ATTR(DATEDIFF("month",[Dates], [Choose Current Month]))<0

and ATTR(DATEDIFF("month",[Dates],[Choose Current Month] ))>-2

THEN [(Actuals + Planned) IGM$]

ELSEIF [Choose IGM$/NNSP]="NNSP"

AND [Choose Period]=3

AND ATTR(DATEDIFF("month",[Dates], [Choose Current Month]))<0

and ATTR(DATEDIFF("month",[Dates],[Choose Current Month] ))>-4

THEN [(Actuals + Planned) NNSP]

ELSEIF [Choose IGM$/NNSP]="IGM$"

AND [Choose Period]=3

AND ATTR(DATEDIFF("month",[Dates], [Choose Current Month]))<0

and ATTR(DATEDIFF("month",[Dates],[Choose Current Month] ))>-4

THEN [(Actuals + Planned) IGM$]

ELSEIF [Choose IGM$/NNSP]="NNSP"

AND [Choose Period]=6

AND ATTR(DATEDIFF("month",[Dates], [Choose Current Month]))<0

and ATTR(DATEDIFF("month",[Dates],[Choose Current Month] ))>-7

THEN [(Actuals + Planned) NNSP]

ELSEIF [Choose IGM$/NNSP]="IGM$"

AND [Choose Period]=6

AND ATTR(DATEDIFF("month",[Dates], [Choose Current Month]))<0

and ATTR(DATEDIFF("month",[Dates],[Choose Current Month] ))>-7

THEN [(Actuals + Planned) IGM$]

END 

Can anyone help with how to work around this error?

Thanks. 

Answer 1

You can't able to mix row-level and aggregate level calculations, If you are using a measure you have to have all aggregate level

To know about Tableau, join our Tableau Certification

Do visit this YouTube video to know more about the Tableau training.