topic Re: SUM with 2 x IF in App Development

SUM with 2 x IF

Daryn — Fri, 22 Apr 2022 07:25:02 GMT

Hi,

I have a table with Dimension for customer & currency. I then have measures summing values (one for UK spend, one for GmbH spend).

We have a basic preset internal transaction exchange rate (say 1.2 for now).

I can convert the GBP using-

Sum ({<WAERK = {'GBP'}>}[ZDE_UMSATZ.NETWR]) + Sum ({<WAERK = {'GBP'}>}NETWR) * 1.2

(blacked out names).

I then tried to turn the expression into a SUM IF. So that the last column 'Totals in €' will include the € currency values as they are ) no multiplication or such.

At some point I will look to include another IF for the $ currency values, but that hasn't got to be now!

WAERK = currency

ZDE_UMSATZ.NETWR = GmbH values

NETWR = UK values

My currencies are as in the table 'GBP', 'EUR', 'USD'

I thought the below would be correct, but it isn't.

=SUM (IF (WAERK -= {'EUR'},[ZDE_UMSATZ.NETWR]+[NETWR]*1.2), IF (WAERK = {'EUR'},[ZDE_UMSATZ.NETWR]+[NETWR]))

I hope that makes sense! Thanks as always for any advice or solution.

Happy Friday!

Regards, Daryn.

Re: SUM with 2 x IF

brunobertels — Fri, 22 Apr 2022 07:54:27 GMT

may be this

=SUM (IF (WAERK -= {'EUR'},[ZDE_UMSATZ.NETWR]+[NETWR]*1.2), IF (WAERK = {'EUR'},[ZDE_UMSATZ.NETWR]+[NETWR]))

sum( if( WAERK<>'EUR',

[ZDE_UMSATZ.NETWR]+[NETWR]*1.2),

if(WAERK='EUR' ,

ZDE_UMSATZ.NETWR]+[NETWR]))

Re: SUM with 2 x IF

Daryn — Fri, 22 Apr 2022 07:59:28 GMT

Hi Bruno,

Thanks for responding 👍

Unfortunately that gives ;

Re: SUM with 2 x IF

brunobertels — Fri, 22 Apr 2022 09:08:55 GMT

Hoi

Sorry my bad

so may be this

IF(WAERK='EUR',SUM([ZDE_UMSATZ.NETWR]) + SUM([NETWR]),IF(WAERK<>'EUR',

SUM([ZDE_UMSATZ.NETWR])+SUM(NETWR]*1.2) )

Re: SUM with 2 x IF

Daryn — Fri, 22 Apr 2022 09:23:52 GMT

Hi and again, thank you.

Unfortunately still an error (I can't see where though).

Re: SUM with 2 x IF

brunobertels — Fri, 22 Apr 2022 10:26:22 GMT

IF(WAERK='EUR',SUM([ZDE_UMSATZ.NETWR]) + SUM([NETWR]),IF(WAERK<>'EUR',

SUM([ZDE_UMSATZ.NETWR])+SUM(NETWR]*1.2) ) )

Re: SUM with 2 x IF

Daryn — Fri, 22 Apr 2022 10:42:43 GMT

Hi Bruno,

Again still an error. I can't see why though!

However following your guidance, I have it working with a nested IF:

IF (WAERK='EUR',SUM([ZDE_UMSATZ.NETWR]) + SUM([NETWR]),
IF (WAERK='GBP',SUM([ZDE_UMSATZ.NETWR]*1.2) + SUM([NETWR]*1.2)
)
)

I can add another If for the USD.

Really appreciate your replies and guidance 👍, thank you. Have a great weekend.