topic Re: Substraction script returns wrong values in Connectivity & Data Prep

Substraction script returns wrong values

JanArnold — Thu, 03 Mar 2022 13:32:00 GMT

My substraction returns the wrong values . First orderintake culumn shows a (correct) measure and the second shows the script.

I use this script and tried al kinds of code to make it work, but it keeps returning the wrong values. Does anyone have an idea why this goes wrong?

I use this script code:

if(isnull([vorigemaandContractwaardemaandelijks]),[Contractwaardemaandelijks],[Contractwaardemaandelijks]-[vorigemaandContractwaardemaandelijks]) AS Orderintake_Period

Simply put:

if(isnull(LASTMONTH), MONTH, MONTH - LASTMONTH) AS Orderintake_Period

Re: Substraction script returns wrong values

Or — Thu, 03 Mar 2022 13:38:08 GMT

LastMonth looks to be zero, not null?

Re: Substraction script returns wrong values

JanArnold — Fri, 04 Mar 2022 07:04:05 GMT

Correct, and then it should substract 'MONTH - LASTMONTH'. It should return: -5696-0 = -5696. However it does return a 0. I'm clueless as to why.

Re: Substraction script returns wrong values

Or — Sat, 05 Mar 2022 18:22:14 GMT

No idea what the actual underlying data is, so I couldn't begin to guess. I suggest breaking it down into two fields, one which returns Month and the other which returns lastmonth (but otherwise with the exact same formula), which should pinpoint where the issue is, and work from there.