topic Nestled IF with Match and AND in calculated field in App Development

Nestled IF with Match and AND in calculated field

OysteinT — Sat, 16 Nov 2024 06:36:15 GMT

Hi,

I'm trying to create this logic:

I have a table with amongst others the fields "Type" (which contains amongst others the entries 'Piece' and 'Hours'), "Payroll" (which contains 'Hourly' and 'Monthly') "Account" (which contains 'F040', 'F0950', 'N0100' amongst others), "Value" (which contains a value), "Postings" (which contains a number value), "Pay" which contains hourly payment.

I am trying to build a logic like this:
IF ([Type] = 'Piece' , [Posting]*[Value],
IF=[Type] = 'Hours',
IF([Payroll]='Monthly' AND Match([Account],'F040', 'F0950', 'N0100'),0,[Posting]*[Value]*[Pay])
,0)
)

Basically if Type = Piece, just calulate Posting with Value.
IF Type = 'Hours' it needs to first check if Payroll is 'Monthly' and then set it to 0 if the account matches the list given. For the rest of the type 'Hours' it will calculate as per the forumla.

If type is not 'Piece' or 'Hours' it should set value to 0.

I first tried using OR instead of the MATCH, but figured that might be wrong.

The error I keep getting is "Missing right paranthesis"...

Re: Nestled IF with Match and AND in calculated field

marcus_malinow — Wed, 13 Feb 2019 13:16:21 GMT

IF ([Type] = 'Piece' , [Posting]*[Value],
IF([Type] = 'Hours',
IF([Payroll]='Monthly' AND Match([Account],'F040', 'F0950', 'N0100'),0,[Posting]*[Value]*[Pay])
,0)
)

Re: Nestled IF with Match and AND in calculated field

OysteinT — Wed, 13 Feb 2019 15:35:02 GMT

that is what I have tried for a long time now to do, but I keep getting an error:

Missing right parenthesis.

My exact code is:

IF ([Type] = 'stykk', [Posting (Seconds)]*[Verdi],
	IF ([Type]='timelønn',
    	IF([Lønnstype]='FASTLØNN' AND Match([Account ID],'F040','F0950','N0100'),0,[Posting (Seconds)]*[Verdi]*[Timel])
        ,0)
        )

I have tried several of the steps on their own and they work (espesially the match one). But all together it fails.... 😞

Re: Nestled IF with Match and AND in calculated field

ToniKautto — Thu, 14 Feb 2019 11:22:08 GMT

That works on my side with Qlik Sense Nov 2018 . What release are you using?

Re: Nestled IF with Match and AND in calculated field

OysteinT — Thu, 14 Feb 2019 11:54:42 GMT

it looks like we're running Qlik Sense June 2018 on our server.

I'll take a peek at downloading the newest version privately at home this weekend and see if I can get it to work there.

Re: Nestled IF with Match and AND in calculated field

ToniKautto — Thu, 14 Feb 2019 23:28:13 GMT

The expression is valid also in June 2018 on my side.

IF ([Type] = 'stykk', [Posting (Seconds)]*[Verdi],
IF ([Type]='timelønn',
IF([Lønnstype]='FASTLØNN' AND Match([Account ID],'F040','F0950','N0100'),0,[Posting (Seconds)]*[Verdi]*[Timel])
,0)
)

Re: Nestled IF with Match and AND in calculated field

OysteinT — Fri, 15 Feb 2019 06:38:35 GMT

Very strange.

If I use the forumla as a master item (measure) in a sheet it works fine, but if I try to create a calculated field it does not work (it seems to hang up on the "AND" statement and gives the paranthesis error)