Sum of sums

[DellaCruze]

Dear Community,

I am struggling to get the sum of two sums in a graph as a header.

The Set analysis is as follows:

='Summe = '& num(fabs(sum({<KAL_Geschäftsjahr_DU={'19/20'},Bewegungsart={'711','713','715','717','712','714','716','718'}>}[Menge in ErfassME])),'#.000,00')&'
WA ='& num(fabs(sum({<KAL_Geschäftsjahr_DU={'19/20'},Bewegungsart={'711','713','715','717'}>}[Menge in ErfassME])),'#.000,00') &'
WE ='& num(sum({<KAL_Geschäftsjahr_DU={'19/20'},Bewegungsart={'712','714','716','718'}>}[Menge in ErfassME]),'#.000,00')

As a result i get the following statement:

But instead of calculating 8.458,95 - 6.409,80 = 2.049,15 I want it to return 

8.458,95 + 6.409,80 = 14.867,75

 Is there a way to get this done? Maybe with aggregation function?
Additionally it is to mention that Numbers belonging to "Bewegungsart = 711, 713, 715, 717" are negative(they have a minus - in front).

Thank you in advance

[avinashelite]

Try like this 

1. Use FAB function and convert negative number to positive and then sum it up 

2. Currently you have already calculated the sum separately rite, store the same in 2 variable and use them to sum it up... That would be even more easier

If your not able to resolve please provide the same data/qlik app will help you

[stevejoyce · Accepted solution]

'Summe = '& num(

fabs(sum({<KAL_Geschäftsjahr_DU={'19/20'},Bewegungsart={'711','713','715','717'}>}[Menge in ErfassME]))

+

sum({<KAL_Geschäftsjahr_DU={'19/20'},Bewegungsart={'712','714','716','718'}>}[Menge in ErfassME])

,'#.000,00')&'
WA ='& num(fabs(sum({<KAL_Geschäftsjahr_DU={'19/20'},Bewegungsart={'711','713','715','717'}>}[Menge in ErfassME])),'#.000,00') &'
WE ='& num(sum({<KAL_Geschäftsjahr_DU={'19/20'},Bewegungsart={'712','714','716','718'}>}[Menge in ErfassME]),'#.000,00')

[DellaCruze]

Thank you very much @stevejoyce ,

your solution fits perfectly. I was not aware of the possibility to use the operator + within the num Columns.

Best Regards

[Kushal_Chawda]

@DellaCruze  not sure what you are trying to achieve. 

Do you want , "Summe = 2.049,15 

                                                 8.458,95 + 6.409,80 = 14.867,75"

[DellaCruze]

@Kushal_Chawda 

no this was the result i got.
i wanted the sum of  "8.458,95 + 6.409,80" which is "14.867,75."

The problem was that the operator "+ "was not summing up outside of the num-function.
 Thanks to stevejoyce solution I was able to change the function properly.

Do you have any other ideas how to fix it?

[Kushal_Chawda · Accepted solution]

@DellaCruze  may be better way would be using rangesum 

num(rangesum( fabs(sum({<KAL_Geschäftsjahr_DU={'19/20'},Bewegungsart={'711','713','715','717'}>}[Menge in ErfassME])),
sum({<KAL_Geschäftsjahr_DU={'19/20'},Bewegungsart={'712','714','716','718'}>}[Menge in ErfassME])),'#.000,00')

[DellaCruze]

You're right!

This solution works too as wished.
Thank you for your response.

Best Regards