Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Kat92
Contributor III
Contributor III

Set Analysis: Aggr sum and count

Hi,

I have created the following calculation and want to calculate the difference in sum of Goal and count of Actual. What I need is to calculate this difference in ID level and then sum the difference and report in the ColumnI levels. Finally in case this is negative want to replace with 0.

 

if(
(Sum(Aggr(sum( {< Category={'Plan'}>} [Goal]),
[Column1], [Column2],[Column3],[Column4],ID)))
-
Sum(Aggr(Count(DISTINCT {< Category={'Calls'}>} [Actual]),
[Column1], [Column2],[Column3],[Column4],ID))>0
,
(Sum(Aggr(sum( {< Category={'Plan'}>} [Goal]),
[Column1], [Column2],[Column3],[Column4],ID)))
-
(
Sum(Aggr(Count(DISTINCT {< Category={'Calls'}>} [Call Name]),
[Column1], [Column2],[Column3],[Column4],ID)))
,
0)

 

The problem is that even if the calculated outcome for the 2 separate seems correct when I do the substraction it shows less than what should.

Could someone please assist?

Thanks in advance

Labels (4)
2 Replies
TcnCunha_M
Creator II
Creator II

could you please explain a little bit better what you mean by 2 separate?
if you try to do like this

if(
(Sum(Aggr(sum( {< Category={'Plan'}>} [Goal]),
[Column1], [Column2],[Column3],[Column4],ID)))
-
Sum(Aggr(Count(DISTINCT {< Category={'Calls'}>} [Actual]),
[Column1], [Column2],[Column3],[Column4],ID))>0
,
1,
0)

 

And tell me if have 1 and 0 in the right place.

As you think, so shall you become.
marcus_sommer

You may try:

rangemax(0,
Sum(Aggr(
   sum( {< Category={'Plan'}>} [Goal]) - Count(DISTINCT {< Category={'Calls'}>} [Actual]),

[Column1], [Column2],[Column3],[Column4],ID)))