% of when using sum of multiple fields gives wrong result

Daryn — Wed, 06 Jul 2022 09:42:44 GMT

Hi to all and thanks in advance as always for your help and support.

I have to sums that both work correctly on their own.

Sum 1 (sum of purchasing spend)=

Sum({< [ZKPI_PUR_RATIO.PERIO.autoCalendar.Year] = {"$(=Year(Today()))"}>} [ZKPI_PUR_RATIO.EKWRT])

Sum 2 (sum of sales)=

Sum ({< [FKDAT.autoCalendar.Year]= {"$(=year(Today()))"} >} [NETWR])
+ SUM ({< [FKDAT.autoCalendar.Year]= {"$(=year(Today()))"} >}[DKK_€])
+ SUM ({< [FKDAT.autoCalendar.Year]= {"$(=year(Today()))"} >}[GBP_€])
+ SUM ({< [FKDAT.autoCalendar.Year]= {"$(=year(Today()))"} >}[HUF_€])

Results sum 1 on left sum2 on right:

However, when I try to combine these sums and divide then *100 for a % (as you normally would), I get the wrong result.

For example they should result in approx 57%

The fields in sum 2 are the conversions of the different currencies into €. Each of these originate from a separate table.

Sum({< [ZKPI_PUR_RATIO.PERIO.autoCalendar.Year] = {"$(=Year(Today()))"}>} [ZKPI_PUR_RATIO.EKWRT])
/
Sum ({< [FKDAT.autoCalendar.Year]= {"$(=year(Today()))"} >} [NETWR])
+ SUM ({< [FKDAT.autoCalendar.Year]= {"$(=year(Today()))"} >}[DKK_€])
+ SUM ({< [FKDAT.autoCalendar.Year]= {"$(=year(Today()))"} >}[GBP_€])
+ SUM ({< [FKDAT.autoCalendar.Year]= {"$(=year(Today()))"} >}[HUF_€])*100

results in -

As usual, I am sure my lack of Qlik interpretation is to blame, but they say there is never a silly question!

What am I doing wrong please?

Thanks kindly, Daryn.

Re: % of when using sum of multiple fields gives wrong result

Daryn — Wed, 06 Jul 2022 11:22:14 GMT

Fixed it with;

=(Sum({< [ZKPI_PUR_RATIO.PERIO.autoCalendar.Year] = {"$(=Year(Today()))"}>} [ZKPI_PUR_RATIO.EKWRT])
/
(Sum ({< [FKDAT.autoCalendar.Year]= {"$(=year(Today()))"}, WAERK = {'EUR'} >} [NETWR])
+ (SUM ({< [FKDAT.autoCalendar.Year]= {"$(=year(Today()))"} >}[DKK_€])
+ (SUM ({< [FKDAT.autoCalendar.Year]= {"$(=year(Today()))"} >}[GBP_€])
+ (SUM ({< [FKDAT.autoCalendar.Year]= {"$(=year(Today()))"} >}[HUF_€]))))))

and displaying as;

Shown under the spend gauge.

topic % of when using sum of multiple fields gives wrong result in App Development

% of when using sum of multiple fields gives wrong result

Re: % of when using sum of multiple fields gives wrong result