Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Laxbax
New Contributor

SUM IF * IF THEN ELSE

Hello 


I'm having a problem getting my total value calculated based on "confirmation key".
So far i've managed to match the currency and multiply the net value by exchange rate.

I want to mulitply the converted value based on "Confirmation key" Where it should prioritze LA>AB>with no key

I'm trying to make an expression that first multiplies , all 'REM QTY' with Confirmation key = 'LA' ,
IF([Confirmation key]='LA',[REM QTY],

if the confirmation key is not <> LA then mulitply all 'REM QTY' with Confirmation key = 'AB'
IF([Confirmation key]<>'LA', [Confirmation key]='AB'*[REM QTY],

and if there is no 'AB,  then just use REM QTY
IF([Confirmation key]<>'AB',[REM QTY])))

At the moment it only gives me the value of all LA Confirmation keys, not all the AB keys that have no LA and no value of the confirmation keys that have no LA or AB

 

=SUM(
IF([Currency]='SEK', [Net Price_NETPR]* 1,
IF([Currency]='NOK', [Net Price_NETPR]* 1.089292,
IF([Currency]='DKK', [Net Price_NETPR]* 1.4145,
IF([Currency]='EUR', [Net Price_NETPR]* 10.5581,
IF([Currency]='GBP',[Net Price_NETP.]* 11.7546,
IF([Currency]='USD',[Net Price_NETPR]* 9.2713,)))))))
*
IF([Confirmation key]='LA',[REM QTY],
IF([Confirmation key]<>'LA', [Confirmation key]='AB'*[REM QTY],
IF([Confirmation key]<>'AB',[REM QTY])))

Tags (2)
Labels (1)
1 Solution

Accepted Solutions
Partner
Partner

Re: SUM IF * IF THEN ELSE

Hi Laxbax,

Try this:

=SUM(
IF([Currency]='SEK', [Net Price_NETPR]* 1,
IF([Currency]='NOK', [Net Price_NETPR]* 1.089292,
IF([Currency]='DKK', [Net Price_NETPR]* 1.4145,
IF([Currency]='EUR', [Net Price_NETPR]* 10.5581,
IF([Currency]='GBP',[Net Price_NETP.]* 11.7546,
IF([Currency]='USD',[Net Price_NETPR]* 9.2713,)))))))
*
IF([Confirmation key]='LA',[REM QTY],
IF([Confirmation key]<>'LA' and [Confirmation key]='AB',[REM QTY],
IF([Confirmation key]<>'AB',[REM QTY])))

But last part always return [REM QTY], so why not do this?

=SUM(
IF([Currency]='SEK', [Net Price_NETPR]* 1,
IF([Currency]='NOK', [Net Price_NETPR]* 1.089292,
IF([Currency]='DKK', [Net Price_NETPR]* 1.4145,
IF([Currency]='EUR', [Net Price_NETPR]* 10.5581,
IF([Currency]='GBP',[Net Price_NETP.]* 11.7546,
IF([Currency]='USD',[Net Price_NETPR]* 9.2713,)))))))
*
[REM QTY])

Or are you missing a field in the part: IF([Confirmation key]<>'LA', [Confirmation key]='AB' MISSINGFIELD *[REM QTY],

Then you can use:

=SUM(
IF([Currency]='SEK', [Net Price_NETPR]* 1,
IF([Currency]='NOK', [Net Price_NETPR]* 1.089292,
IF([Currency]='DKK', [Net Price_NETPR]* 1.4145,
IF([Currency]='EUR', [Net Price_NETPR]* 10.5581,
IF([Currency]='GBP',[Net Price_NETP.]* 11.7546,
IF([Currency]='USD',[Net Price_NETPR]* 9.2713,)))))))
*
IF([Confirmation key]='LA',[REM QTY],
IF([Confirmation key]<>'LA' and [Confirmation key]='AB',[MissingField] *[REM QTY],
IF([Confirmation key]<>'AB',[REM QTY])))

Jordy

Climber

Work smarter, not harder

View solution in original post

2 Replies
Partner
Partner

Re: SUM IF * IF THEN ELSE

Hi Laxbax,

Try this:

=SUM(
IF([Currency]='SEK', [Net Price_NETPR]* 1,
IF([Currency]='NOK', [Net Price_NETPR]* 1.089292,
IF([Currency]='DKK', [Net Price_NETPR]* 1.4145,
IF([Currency]='EUR', [Net Price_NETPR]* 10.5581,
IF([Currency]='GBP',[Net Price_NETP.]* 11.7546,
IF([Currency]='USD',[Net Price_NETPR]* 9.2713,)))))))
*
IF([Confirmation key]='LA',[REM QTY],
IF([Confirmation key]<>'LA' and [Confirmation key]='AB',[REM QTY],
IF([Confirmation key]<>'AB',[REM QTY])))

But last part always return [REM QTY], so why not do this?

=SUM(
IF([Currency]='SEK', [Net Price_NETPR]* 1,
IF([Currency]='NOK', [Net Price_NETPR]* 1.089292,
IF([Currency]='DKK', [Net Price_NETPR]* 1.4145,
IF([Currency]='EUR', [Net Price_NETPR]* 10.5581,
IF([Currency]='GBP',[Net Price_NETP.]* 11.7546,
IF([Currency]='USD',[Net Price_NETPR]* 9.2713,)))))))
*
[REM QTY])

Or are you missing a field in the part: IF([Confirmation key]<>'LA', [Confirmation key]='AB' MISSINGFIELD *[REM QTY],

Then you can use:

=SUM(
IF([Currency]='SEK', [Net Price_NETPR]* 1,
IF([Currency]='NOK', [Net Price_NETPR]* 1.089292,
IF([Currency]='DKK', [Net Price_NETPR]* 1.4145,
IF([Currency]='EUR', [Net Price_NETPR]* 10.5581,
IF([Currency]='GBP',[Net Price_NETP.]* 11.7546,
IF([Currency]='USD',[Net Price_NETPR]* 9.2713,)))))))
*
IF([Confirmation key]='LA',[REM QTY],
IF([Confirmation key]<>'LA' and [Confirmation key]='AB',[MissingField] *[REM QTY],
IF([Confirmation key]<>'AB',[REM QTY])))

Jordy

Climber

Work smarter, not harder

View solution in original post

Highlighted
Laxbax
New Contributor

Re: SUM IF * IF THEN ELSE

Thank you, I was missing a crucial field