cancel
Showing results for 
Search instead for 
Did you mean: 

Sum of Amount based on filter on other field

Former Member
0 Kudos

I am trying to create a report where sum of amount would be displayed in 3 different columns based on what is in EDLCode field.

So,

If EDLCode IN (501,502,503) Then Sum(Amount) in Column1

If EDLCode IN (505,506) Then Sum(Amount) in Column2

If EDLCode IN (1500) Then Sum(Amount) in Column3

Let me know if this makes sense. I am getting the report to work when I put fields in detail section but I need only one line per employee, so I am using Group Header and it shows 0 in column 2 and 3.

Final data should look like:

EmployeeName Column1 Column2 Column3

My ultimate aim is to concatenate all this fields in 1 field and be able to send as fixed width file.

Accepted Solutions (1)

Accepted Solutions (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi Parag,

Here's what you need to do:

1) Create a formula (@Column1) with this code:

If {EDLCode} IN [501,502,503] Then {Amount}

2) Go to Insert Summary > choose the formula field from the list as the Field to Summarize > Choose 'Sum' as the summary operation > Under Summary Location choose the correct Group and click OK

3) Repeat steps 1 and 2 for the other two columns however, make sure you change the EDL codes for each formula.

You can also do this via the Running Total Option however you won't be able to place the sum on the Group Header, it works only on the Group Footer.

-Abhilash

Former Member
0 Kudos

Abhilash,

Thank you for the reply,

Your solution is giving me the total of all amount that have those EDLCode, I need it grouped by employees and also I need to store sum of column1,2 and 3 in a field so I can use that to create a fixed width file.

EmployeeName&''&(Sum of Column1)&''&(Sum of Column2)&''&(Sum of Column3)

One record per employee and sum grouped by employee.

Hope that makes sense.

Former Member
0 Kudos

Abhilash,

I think I got it resolved, your solution helped in decoding it.

I created the summary field as you suggested and then created another formula field and used that summary field in it, but it was giving me total sum, instead of per employee sum.

So I grouped it on Employee and got it to working in the footer, so the final formula looked like.

TotalAmount = (Sum ({@Column1},{PREH.Employee})

where @Column1 = IF {EDLCode} IN [501,502,503} THEN {Amount} and then created a summary field for @Column1

abhilash_kumar
Active Contributor
0 Kudos

Great!

Point #2 where I wrote " Under Summary Location choose the correct Group and click OK", I assumed that there exists a Group on the report already!


The Solution needs a group.

-Abhilash

Answers (0)