Nestled IF with Match and AND in calculated field

[OysteinT]

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"... 

[marcus_malinow]

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

[OysteinT]

Hi

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.... 😞 

[ToniKautto]

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

[OysteinT]

Hi

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. 

[ToniKautto]

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)
)

[OysteinT]

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)