Quick question about sum function

[#1]

Good morning, I am trying to sum all the values that match one specific condition but I can sort it out. If first I show all the values I want to sum it is correct:

EventId       RISK REVENUE  AMOUNT    1

76672         $25,131.50

176674       $25,131.50

176671       $31,600.00

176673       $31,600.00

I used the dimension EventId and this expression:

= IF( (IsNull(NewEventDescription)=0 OR IsNull(NewProjectManager)=0 OR IsNull(NewProjectName)=0 OR IsNull(NewServiceLine)=0 OR IsNull(NewTaskNumber)=0 OR IsNull(NewProjectNumber)=0 OR IsNull(NewEventDate)=0 OR IsNull(NewProjectDescription)=0 OR IsNull(NewTaskName)=0 OR IsNull(NewRevValue)=0 OR IsNull(NewBillValue)=0 OR IsNull(NewCustomerName)=0 OR isnull(DateNewLineAdded)=0 ) AND EventDate>QuarterStart(TodayDate) AND EventDate<QuarterEnd(TodayDate) AND RevenueFlag='N' AND BilledFlag='N' AND ProjectManager='David Williams',

                              (ProjectedRevenue)

)

but when I use the sum function it prints a result doesnt make any sense cos it doesnt show the total that should be $113.463:

EventId  RISK REVENUE  AMOUNT

176672  $1,759,205.00

176671  $2,212,000.00

176674  $3,518,410.00

176673  $4,424,000.00

I used the dimension EventId and this expression (the same than before but with the sum function at the begining:

= sum(IF( (IsNull(NewEventDescription)=0 OR IsNull(NewProjectManager)=0 OR IsNull(NewProjectName)=0 OR IsNull(NewServiceLine)=0 OR IsNull(NewTaskNumber)=0 OR IsNull(NewProjectNumber)=0 OR IsNull(NewEventDate)=0 OR IsNull(NewProjectDescription)=0 OR IsNull(NewTaskName)=0 OR IsNull(NewRevValue)=0 OR IsNull(NewBillValue)=0 OR IsNull(NewCustomerName)=0 OR isnull(DateNewLineAdded)=0 ) AND EventDate>QuarterStart(TodayDate) AND EventDate<QuarterEnd(TodayDate) AND RevenueFlag='N' AND BilledFlag='N' AND ProjectManager='David Williams',

                              (ProjectedRevenue)

)

)

I didnt selected the option "Full accumulation" in Expressions tab.

Could you please tell me what I am doing wrong?

Thanks a lot in advance.

[#2]

Hi Jonathan, thank you for you long reply, he hee.

What do you mean with "you used ProjectedRevenue instead of Sum(ProjectedRevenue) in your expression"? I always use the expression Sum(ProjectedRevenue) to show the grand total.

I was already aware of the fact that Qlikview is multiplying 70* ProjectedRevenue, but what I dont understand is why there are 70 rows for the same EventId.

There is no way to sum the ProjectedRevenue distinct EventId or something like that to avoid to sum up 70 times the same amount?

Thanks a lot mate.

[jonathandienst]

Hi

yomarcos2 wrote:

There is no way to sum the ProjectedRevenue distinct EventId or something like that to avoid to sum up 70 times the same amount?

That possible, but is merely treating the symptom rather than curing the problem. I doubt that there is a shortcut to this problem.

Regards,

Jonathan