topic if function not working in QlikView

if function not working

timmy — Thu, 22 Jul 2010 14:13:50 GMT

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 — Thu, 22 Jul 2010 14:17:38 GMT

Hi,

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

mihaiturturica — Thu, 22 Jul 2010 14:18:13 GMT

Hi,

Create a variabile

Let vMaxYear = max(Year)

and use this:

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

Mihai

Thu, 22 Jul 2010 14:23:31 GMT

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 — Thu, 22 Jul 2010 14:29:33 GMT

I have tried all the solutions but nothing is working.

tnx for the replies...

tabletuner — Thu, 22 Jul 2010 14:30:24 GMT

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

where vMaxYear = max(Year)

timmy — Thu, 22 Jul 2010 14:34:18 GMT

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

"Aggregation expressions not allowed in GROUP BY clause"

Thu, 22 Jul 2010 14:45:17 GMT

can you attach an example?

tabletuner — Thu, 22 Jul 2010 14:46:12 GMT

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