6 Replies Latest reply on Oct 16, 2019 10:16 AM by Neha Vaishnavi

# HELP in choosing right function for mixing aggregate and non-aggregate functions error

Hi,
I am trying to fix the following error which is highlighted in red.
I tried using ATTR FLOAT but I get error when I use min or max then I dont get error. Problem is, this is a date and I want to know which is the right function to use for date and gives me result.
IF min(MONTH([Date]))<= min(MONTH({fixed: max([Month Year])}))

AND min(YEAR([Date]))  = YEAR(TODAY())-1

THEN sum([A])

+ sum([B])

END

Please experts out there help me out. Also I tried using sum in the beginning of all functions but then I don't get two values summing up. only one value is returning instead of summing up two values which is (Revenue based on parameter):

Example:

SUM(IF (MONTH([Date]))<= (MONTH({fixed: max([Month Year])}))

AND (YEAR([Date]))  = YEAR(TODAY())-1

THEN ([A])

+ ([B])

END)

And I get the value of Revenue based on parameter only instead of (A+B).

Message was edited by: Neha Vaishnavi

• ###### 1. Re: HELP in choosing right function for mixing aggregate and non-aggregate functions error

Neha,

Try to do your logic at the row level and then do the aggregations.

Something like

SUM(

IF (MONTH([Date]))<= (MONTH({fixed: max([Month Year])}))

AND (YEAR([Date]))  = YEAR(TODAY())-1

THEN [Revenue based on discount] end

)

+

SUM(

IF (MONTH([Date]))<= (MONTH({fixed: max([Month Year])}))

AND (YEAR([Date]))  = YEAR(TODAY())-1

THEN [Revenue based on parameter] end

)

1 of 1 people found this helpful
• ###### 2. Re: HELP in choosing right function for mixing aggregate and non-aggregate functions error

Thanks Michel for the amazing help. But it is not working, attached is my sample data. Please let me know what I might be doing it wrong.

• ###### 3. Re: HELP in choosing right function for mixing aggregate and non-aggregate functions error

Neha,

you have to save your file as a .twbx  ntot  .twb

The .twb  does not contain the data and I cannot connect to your datasource.

1 of 1 people found this helpful
• ###### 4. Re: HELP in choosing right function for mixing aggregate and non-aggregate functions error

Sure, here it is . Thank you.

• ###### 5. Re: HELP in choosing right function for mixing aggregate and non-aggregate functions error

Neha,

The problem is because you have nulls and  in Tableau    1 + null returns null and not  1.

So you have to "if null then 0"  your  measures.  You can do that using the  ZN() function  (Zero Null).

(ZN( [A] ) + ZN( [B] )) And for your   if function, I am not sure to understand the business logic you are trying to implement, but maybe this is what you are looking for

SUM(IF (MONTH([Date]))<= {MAX(MONTH( [Month Year] ))}

AND (YEAR([Date]))  = YEAR(TODAY())-1

THEN (ZN( [A] ) +ZN(  [B] ))

END)

1 of 1 people found this helpful
• ###### 6. Re: HELP in choosing right function for mixing aggregate and non-aggregate functions error

Wowwww!!!!!!!!

I was spending too much time fixing it and googling. Unfortunately did not find right solution from google or myself. Posting it here was my last try and haven't expected any response to fix my issue this fast.

Thanks a lot Michel. You are the best.