10 Replies Latest reply on Oct 11, 2018 11:43 AM by ShivaRam Chennapragada

Cannot mix aggregate and non-aggregate in IF

Hi, I got this error message when trying to get the L3M of gross margin, see my calculation below:

IF [Date] <= TODAY()

AND

(

DATEDIFF('month',[Date],TODAY())=1

or

DATEDIFF('month',[Date],TODAY())=2

or

DATEDIFF('month',[Date],TODAY())=3

)

then [Gross Margin] END

And the calculation for [Gross Margin]= SUM([Sales])-SUM([COGS])

Can anyone help me fix the IF statement?

• 1. Re: Cannot mix aggregate and non-aggregate in IF

Did you try this?

SUM(

IF [Date] <= TODAY()

AND

(

DATEDIFF('month',[Date],TODAY())=1

or

DATEDIFF('month',[Date],TODAY())=2

or

DATEDIFF('month',[Date],TODAY())=3

)

then [Gross Margin] END

)

• 2. Re: Cannot mix aggregate and non-aggregate in IF

I did, I got the same error message.

• 3. Re: Cannot mix aggregate and non-aggregate in IF

I can think two ways.

Either put Date field in ATTR function  attr( date)

OR

change Gross Margin calculation to -  [Gross Margin]= [Sales]  -  [COGS]   i.e. Remove SUM from sales and COGS field.    I will prefer this one.

1 of 1 people found this helpful
• 4. Re: Cannot mix aggregate and non-aggregate in IF

can you please attach sample workbook

• 5. Re: Cannot mix aggregate and non-aggregate in IF

Thanks for the help, I tried both of your method, however I still have problems.

If I change the Gross Margin calculation to [Gross Margin]= [Sales]  -  [COGS], the result is blank, no value shows up (no error message though).

Same with adding ATTR to date ATTR(Date), the error message is gone, but no value shows up.

• 6. Re: Cannot mix aggregate and non-aggregate in IF

Sometimes I have better luck transforming booleans into more workable numbers. Sometimes I even break each section down and display it (just to make sure it's computing correctly), but that's just my preference.

Try this:

Part 1

AVG(IIF(Date]<[Today] AND (DATEDIFF#1 OR DATEDIFF#2 and so on),1,0))

Part 2

IF [Part 1] = 1 then [Gross Margin] END

Good luck!

1 of 1 people found this helpful

• 8. Re: Cannot mix aggregate and non-aggregate in IF

Hello,

You can try below logic:

Sales =

ZN(IF [Order Date] <= TODAY()

AND (DATEDIFF('month',[Order Date],TODAY())<=3

AND DATEDIFF('month',[Order Date],TODAY())>0)

then [Sales] END)

COGS =

ZN(IF [Order Date] <= TODAY()

AND (DATEDIFF('month',[Order Date],TODAY())<=3

AND DATEDIFF('month',[Order Date],TODAY())>0)

then [Profit] END)

Cal = SUM(Sales)- SUM(COGS)

2 of 2 people found this helpful
• 9. Re: Cannot mix aggregate and non-aggregate in IF

Thanks, ZN() is not necessary in the calculation. I ended up break it to L3M SALE and L3M COGS, and then SUM(L3M SALE)- SUM(L3M COGS)

• 10. Re: Cannot mix aggregate and non-aggregate in IF

I'm surprised how many different ways questions like this can be answered. Learned new things. Thanks community.