Conditional Totals In Report ?

[The Brown Growler]

Hi,

Would anyone please be able to help with a conditional total in a report? What I wish to do is to total the values of a report numeric field only if the value of another text field equals a specific value.

For example I have a monthly breakdown in numeric values of a training program in a field called [Cursus]. The field that contains the other values is called [KPI] What I wish to do is to only sum the [Cursus] field values where the [KPI] field value is equal to "People Trained" (quotation marks only included for illustration)

I have tried using the expression
=IIf([KPI]="People Trained",Sum([Cursus]),0)

However, it just gives me the 0 value when it should be giving a number. There are no null values in the report data, ie, all values are either 0 or a number.

Regards

 

[khawar]

You have to creat a running total field on report

 

[Rich]

=Sum(Iif([KPI]="People Trained",[Cursus],0))

 

[The Brown Growler]

Rich,

Many thanks, that worked a treat.

Regards

 

[The Brown Growler]

Rich,

If you do not mind, I need a bit more assistance with a closely related topic. In my same report as discussed above I also have a column consisting of feedback values re course satisfaction etc.

I tried to apply similar syntax to get an Average using the below formula in the control data

=Avg(IIf([KPI]="People Trained",[Satisfaction],0))

However for a data set of 0.96, 0.95, 0.98, 0.97 and 0.97 the value created by the above formula is given as 0.24. There are no zero values and all the values are as listed. The Average should be coming out at approx 0.96.

If I Sum the field values using the formula:

=Sum(IIf([KPI]="People Trained",[Satisfaction],0))

then I get the correct value in the report of 4.83.

Any help most appreciated.

Regards