Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
bottulf12
New Contributor

Nestled IF with Match and AND in calculated field

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

Labels (3)
6 Replies
marcus_malinow
Valued Contributor III

Re: Nestled IF with Match and AND in calculated field

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

bottulf12
New Contributor

Re: Nestled IF with Match and AND in calculated field

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

 

Employee
Employee

Re: Nestled IF with Match and AND in calculated field

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

Re: Nestled IF with Match and AND in calculated field

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. 

Employee
Employee

Re: Nestled IF with Match and AND in calculated field

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)
)
bottulf12
New Contributor

Re: Nestled IF with Match and AND in calculated field

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)