topic Re: Sum with multiple conditions giving wrong end value in App Development

Sum with multiple conditions giving wrong end value

Daryn — Thu, 07 Oct 2021 12:03:20 GMT

Hello community,

I am still quite new to Qlik Sense and taught through this great community and some Youtubing!

I am a bit stuck with the following as it doesn't give the result expected for the margin as a %

To begin I have a margin calculation that works okay.

* Sum of revenue minus costs divided by revenue (*100 for percentage)

(sum (NETWR) - sum (WAVWR)) / sum ([NETWR])*100

This produces the correct calculation.

I then want to apply this across some other charts but with modified selections.

*Sum where Sales organization = UK99 and distribution channel = WO, revenue minus costs divided by revenue (*100 for percentage)

SUM ({< VKORG = {'UK99'}, VTWEG -={'WO'}>} ((NETWR-WAVWR)/[NETWR]) *100)

I get a result in the millions rather than what I expect at say 40%.

I tried as an IF at the start but with no joy. Is it because I am not summing the individual NETWR, WAVWR etc as in the original Margins calculation?

Sorry but I am completely stuck on this one 😞

Help appreciated.

Regards

Daryn

Re: Sum with multiple conditions giving wrong end value

Or — Thu, 07 Oct 2021 12:08:08 GMT

Your original calculation has three separate sums(), but your new one appears to have all three fields lumped into the same sum(). Try writing the new expression the same way, including the set analysis within each sum.

Re: Sum with multiple conditions giving wrong end value

Daryn — Thu, 07 Oct 2021 12:13:51 GMT

Hi OR,

Thanks for the speedy response!

I get an error 'Nested aggregation is not allowed when trying that'?

SUM ({< VKORG = {'UK99'}, VTWEG -={'WO'}>} (sum (NETWR) - sum (WAVWR)) / sum (NETWR)*100 )

Sorry, await any update.

Thanks Daryn

Re: Sum with multiple conditions giving wrong end value

Or — Thu, 07 Oct 2021 12:24:59 GMT

Keep the same Syntax as the original...

(SUM ({< VKORG = {'UK99'}, VTWEG -={'WO'}>} NETWR)

SUM ({< VKORG = {'UK99'}, VTWEG -={'WO'}>} WAVWR))

(SUM ({< VKORG = {'UK99'}, VTWEG -={'WO'}>} NETWR)

* 100

If that isn't right for what you need, you may have to use aggr() for advanced aggregation of the results for a certain dimension level, but if that's the case, you'll need to be more specific about the structure of your object and aggregation requirements.

Re: Sum with multiple conditions giving wrong end value

Daryn — Thu, 07 Oct 2021 12:37:41 GMT

Hi OR,

Thank you again, that's got it (though it was missing one more ) before the *).

Really appreciate your time and speedy responses.

Regards Daryn