14 Replies Latest reply on Nov 19, 2016 12:40 AM by Marc MOUSSA

# Sum a field conditionally based on another field, and exclude the remain fields from full data

Hi,

I want to take the sum of a field based on another field.

I need it to behave as if I did

SUM( IFF( [Field1] = "ABC", [Field2], 0) )

the problem with this is when I view the full data, the rows where Field1 != 0 are still there.

this works correctly if a make a set, but then I get 2 rows, in and out.

can I have it both ways?

• ###### 1. Re: Sum a field conditionally based on another field, and exclude the remain fields from full data

can you post a workbook with a small dataset ? with the field that is not working ?

• ###### 2. Re: Sum a field conditionally based on another field, and exclude the remain fields from full data

in the workbook I want the sum of all "Baz"s where "Bar" = "X", and I can get that,

but if you look at the full data for either "A" or "B" , the rows where "Bar" = "Y" also show up

• ###### 3. Re: Sum a field conditionally based on another field, and exclude the remain fields from full data

I saw in your answer that you see the other lines showing in the underlying data.

If we look at the following calculated field : SUM( IFF( [Field1] = "ABC", [Field2], 0) )

All the rows are going to show in the underlying data as long as you don't filter them. You'll have only the column with the calculated field with the 0.

• ###### 4. Re: Sum a field conditionally based on another field, and exclude the remain fields from full data

can I filter the data for just 1 calculation?

I need all the data for the report, but only subsets of the data for any given column

• ###### 5. Re: Sum a field conditionally based on another field, and exclude the remain fields from full data

No the filter will apply to all the dataset.

But why do you want to filter the data given that the result is correct.

• ###### 6. Re: Sum a field conditionally based on another field, and exclude the remain fields from full data

A requirement for this report is that only the values that meet the criteria should be included in the full data

• ###### 7. Re: Sum a field conditionally based on another field, and exclude the remain fields from full data

Then use this field on the filter shelf

• ###### 8. Re: Sum a field conditionally based on another field, and exclude the remain fields from full data

but then the data is unavailable for the rest of the report.

if I filter on Bar = "X", then in the second column where it's expecting Bar = "Y" will always be 0

• ###### 9. Re: Sum a field conditionally based on another field, and exclude the remain fields from full data

Then what is important is the result of the calculation.

Is the result of your calculated field correct. what bothers you is the fact that the underlying data is larger then what you expected for the field ?

• ###### 10. Re: Sum a field conditionally based on another field, and exclude the remain fields from full data

I need to know what when into giving me that number

• ###### 11. Re: Sum a field conditionally based on another field, and exclude the remain fields from full data

Then what you must look for is the values of the calculated field that are returned in the underlying data not the rows.

Are you used to sql ?

• ###### 12. Re: Sum a field conditionally based on another field, and exclude the remain fields from full data

Then what you must look for is the values of the calculated field that are returned in the underlying data not the rows.

How do I do that?

• ###### 13. Re: Sum a field conditionally based on another field, and exclude the remain fields from full data

Can you create a row-level calculation that only displays Field2 if Field1 is valid, otherwise is NULL, then sum the following field? In the dummy workbook you posted it would look like this:

if [Bar] == "X" then [Baz] else NULL end

When I sum [Baz], I get 20. When I sum the new field, I get 10. When I count, I get 8 [Baz] and 4 in the new field.

I'm not sure I understand the issue with the rest of the report - the sum will appear correct without filtering if you have either 0 or NULL, but if there is other data on those lines that you need to see, those lines will still appear.

Is this something you could create using two charts and a dashboard? You can make them interact using actions and have one filtered to exclude what you want to exclude and the other including it?

John

• ###### 14. Re: Sum a field conditionally based on another field, and exclude the remain fields from full data

As you can see below The calculated field gives 0 (left column) even though the original data is still showing the underlying data (right column)