1 2 Previous Next 19 Replies Latest reply on Jan 17, 2019 10:06 AM by sajad.bhat

# Help with the formula

The below is formula is valid, but says 'applies a numeric aggregation to a on-numeric field' IF [cancellation_time] = NULL AND [fill_time] = Null THEN 'order_time' ELSEIF [cancellation_time] != NULL AND [fill_time] != Null THEN '[fill_time] - [order_time]' ELSEIF [cancellation_time] != NULL AND [fill_time] = Null THEN '[cancellation_time] - [order_time]' ELSEIF [cancellation_time] = NULL AND [fill_time] != Null THEN '[fill_time] - [order_time]' ELSE 'NULL' END any help is appreciated.

• ###### 1. Re: Help with the formula

All the time fields are timestamps.

• ###### 2. Re: Help with the formula

Hard to tell without knowing the datatypes of each field, but if I reformat your code a bit ...

```IF [cancellation_time] = NULL AND [fill_time] = Null
THEN 'order_time'
ELSEIF [cancellation_time] != NULL AND [fill_time] != Null
THEN '[fill_time] - [order_time]'
ELSEIF [cancellation_time] != NULL AND [fill_time] = Null
THEN '[cancellation_time] - [order_time]'
ELSEIF [cancellation_time] = NULL AND [fill_time] != Null
THEN '[fill_time] - [order_time]'
ELSE 'NULL'
END
```

I'm not sure if you're trying to calculate the difference between 2 dates or not, but you're using NULL in some of the comparisons, but then on line 9 NULL is a string.

Maybe a packaged workbook is in order to understand what you're trying to do

• ###### 3. Re: Help with the formula

Then don't use quotes and check how to use DATEDIFF for the subtractions

• ###### 4. Re: Help with the formula

Chris,

Im calculating difference between two date fields.

What can I use instead of NULL...basically the field there has no value

Also in line number 9, what should I be using instead of NULL

• ###### 5. Re: Help with the formula

It's hard to say because there's not enough information to give an accurate answer.

Can you attach a workbook with some dummy data ?

If the calculated field is a date then none of it will work.

If the calculated field is a string then you won't get the date calculations that you want.

For line 9, I'd use NULL instead of 'NULL', but I've really got no idea what you're trying to achieve so it's hard to say.

1 of 1 people found this helpful
• ###### 6. Re: Help with the formula

Chris,

I have restrictions and can not share my data.

The calculated field should give me time based in seconds or minutes

1 of 1 people found this helpful
• ###### 7. Re: Help with the formula

Definitely don't share your REAL data, but a workbook with some DUMMY data will really help

1 of 1 people found this helpful
• ###### 8. Re: Help with the formula

Chris,

I understand however I have restrictions and can not upload anything from my laptop.

There are three date fields Order_Time, Cancel_Time and FIll_Time. I need to subtract

1 of 1 people found this helpful
• ###### 9. Re: Help with the formula

Hi Kalyan,

The calc: above is returning two data types first condition is returning date whereas rest all are returning Integer.

in case you are trying to find difference in date then you have to change first condition(""Order_time""), Once you change all will return you difference between dates in ""Days".

you can use any calc. and whether last else you state in calc or not it will always give you null only.

IF ISNULL([cancellation_time])=TRUE AND ISNULL([fill_time])=TRUE

THEN ([order_time])-[somedate]

ELSEIF ISNULL([cancellation_time])=FALSE AND ISNULL([fill_time])=FALSE

THEN ([fill_time]-[order_time])

ELSEIF ISNULL([cancellation_time])=FALSE AND ISNULL([fill_time])=TRUE

THEN ([cancellation_time]-[order_time])

ELSEIF ISNULL([cancellation_time])=TRUE

AND ISNULL([fill_time])=FALSE

THEN ([fill_time]-[order_time])

END

In case you found it helpful you can mark it as answered.

1 of 1 people found this helpful
• ###### 10. Re: Help with the formula

Chris,

What can I use instead of somedate.......do I have to populate all the nulls with somedate.

Thank you for your prompt response.

• ###### 11. Re: Help with the formula

I get this error.

'Expected datetime, found Float. Result types from IF expression must match'

• ###### 12. Re: Help with the formula

You can use any date there from which you want to calc difference.

similar way as you have calc. other condition clause's

1 of 1 people found this helpful
• ###### 13. Re: Help with the formula

Yes you will get this error untill you replace it with date diff

1 of 1 people found this helpful
• ###### 14. Re: Help with the formula

Thanks everyone for their contribution.

Im able to ramp it down till here, now how would I use DATEDIFF here for the below formula

IF[order_amount] = [unfilled_amount]

THEN

(IFNULL([cancellation_time], [Fill_Time]) - [Order_Time])

ELSE

[Cancellation_Time] - [Order_Time]

END

1 2 Previous Next