8 Replies Latest reply on Feb 6, 2017 9:36 AM by Jim Dehner

# Calculated Field

Hello,

I have attached a spread sheet which contains test data. What I would like to do is create a filter with "School1" and "School 2" as a filter. If School 1 is selected then I need the Individual rows displayed for "Ed Cur" field, "Ed Ped" field and "Ed scd" with sum of values.

And if School 2 is selected, I need individual rows displayed for  "Iaps Gen", "Iaps MHC" and "Iaps Phd" with the sum of values

How would I accomplish that?

Thanks,

Sue

• ###### 1. Re: Calculated Field

Susan

Could not open your excel file - says it is corrupt could you resend

Jim

• ###### 2. Re: Calculated Field

Hi Jim,

I have attached the file again.

Thanks,

Sue

• ###### 3. Re: Calculated Field

Hi Susan,

One method would be to pivot the columns so you can have the values in the rows.  From there you can create a calculated field like

"

IF [School Desc] = 'School 1' AND
([Pivot Field Names] = 'Ed Cur' or [Pivot Field Names] = 'Ed Ped' or [Pivot Field Names] = 'Ed Scd')

or

[School Desc] = 'School 2' AND
([Pivot Field Names] = 'Iaps Gen' or [Pivot Field Names] = 'Iaps Mhc' or [Pivot Field Names] = 'Iaps Phd')

THEN

[Pivot Field Values]

END

"

If you then filter out the nulls I think it should give you the desired effect.

• ###### 4. Re: Calculated Field

Hi Susan

Thanks for re-sending the data - I agree with with Kang that pivoting the data is the way to go

I did it at the data source level leaving a set of values and field names

Then I did set up a filter Show Data >> If Value >0 then 1 End

Drag the filter to the filter shelf and set the value to 1

drag fields to the columns and schools to the row

Drag a schools tot the  filter shelf and show the filter - when you select schools the null field disappear

Here is a T10.1 copy

it should look like this

Let me know if that helps

Thanks

Jim

1 of 1 people found this helpful
• ###### 5. Re: Calculated Field

Hi Jim,

Would you please show me, how did you pivot the data? Your Screenshot; looks exactly how I wanted to display it.

Thanks,

Sue

• ###### 6. Re: Calculated Field

Good morning

see the link below to the on-line hel

I will walk you through it here

Go to the data source tab

Identify the columns that need to be pivoted (it you data)

Click on the header for the first column (Total Number)

Shift click the header for the column on the right most of your data (Ed scd)

While all the selected columns are highlighted Right click the last column and click on Pivot Data on the box that opens

the several columns that were highlighted will now become 2 columns that you can rename - I just chose Fields and Values

That's all there is to it

In your viz - Fields becomes a dimension that you add to viz and the Values are the Measures

Let me know how it goes

Jim

• ###### 7. Re: Calculated Field

Hello Jim,

It works like a charm.. Thank You so much.

Sue