if function not working

[timmy]

Hey,

i have a problem with the function underneed!!

=

Count (Distinct ( If ( (ShipDate - DueDate) <= 0, if((Fiscal_Year=max(Year),SalesOrderID ))) )

when i try instead of the max(year) just 2009 everything works fine but with the formula is shows nothing.

tnx

[brenner_martina]

Hi,

it can not work if number of brackets are wrong:Count(Distinct (If(ShipDate - DueDate) <= 0, if(Fiscal_Year=max(Year),SalesOrderID )))

[mihaiturturica]

Hi,

Create a variabile

Let vMaxYear = max(Year)

and use this:

Count (Distinct ( If ( (ShipDate - DueDate) <= 0, if((Fiscal_Year=$(vMaxYear),SalesOrderID ))) )

Mihai

[#4]

its your second if command you have an extra set of brackets that arent neccessary. so your if statement has only 1 parameter instead of 2.

try this.

Count (Distinct ( If ( (ShipDate - DueDate) <= 0, if( Fiscal_Year=max(Year),SalesOrderID ) ))

[timmy]

I have tried all the solutions but nothing is working.

tnx for the replies...

[tabletuner · Accepted solution]

try a variable instead of the fieldname like $(vMaxYear)

where vMaxYear = max(Year)

[timmy]

I have tried that but when i reload the script is get a error message:

"Aggregation expressions not allowed in GROUP BY clause"

[#8]

can you attach an example?

[tabletuner]

Define the variable in the variable overview ( i believe in the settings menu).

Dont define it in the script because the variable must be dynamically defined during runtime.

good luck