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.

[martin59]

Hi,

If you use a straight table, you have to do the "sum of rows" in expression tab

Hope that helps you

Martin

[#3]

Thank you for your reply. The thing is that I want to show only the total sum of those values, not the whole table.

Regards.

[martin59]

Ok,

You have to remove your dimension "Event ID"

Is that you want ?

[#5]

Thanks again for your quick answer. If I do that, using a straight table I get the value 11.913.615 (that is wrong cos it should be $113.463). That number seems to be the sum of all of the other wrong values I get when I use the sum function:

EventId  RISK REVENUE  AMOUNT

176672  $1,759,205.00

176671  $2,212,000.00

176674  $3,518,410.00

176673  $4,424,000.00

Regards.

[#6]

So, it is still wrong cos the total should be $113.463 , not 11.913.615

Could you please help me?

[jonathandienst]

Hi

I suggest that you post your model or a sample thereof.

Jonathan

[its_anandrjs]

Hi Yomarcos2,

I think you have to use Sum(Total Column1) like this you get total value.

Regards,

Anand

[#9]

Hi its.anandrjs, with Column1 you mean the EventId? If so, I need to sum "Risk revenue amount", not EventId.

I tried to do sum(total RiskRevenueAmount) but it didnt work either, i got the message "No data to display".

Regards.

[#10]

Hi Jonathan Dienst , this is my DB model, basically I look every row identified by "EventId", and if the fields in that row meet my conditions I want to get the ProjectedRevenue value (in table TblERPData) and add it up to the one I got previously to get the total amount, I mean to sum up all the ProjectedRevenue's that meet my conditions.

THANKS A LOT!