6 Replies Latest reply on Oct 12, 2012 2:12 PM by Luis Campos

# How can I attribute a calculated field aggregation to different dates?

Hello,

Iam working with data that concerns Post-secondary education in Brazil. As an example of my problem I will use a sample of Medical schools. I want to compare the number of students that were admited in 'year t', with the number of students that graduated in 'year t+6' (Medical school lasts for six years.) The data for year is discrete and I need the to informations, admited and graduated, in one axis.

This would be easy to do by altering my data base if I only needed the numbers sumed up for Brazil. The problem is that I need to be albe to filter for Regions and other dimensions (different courses, public or private etc.)

Attached is the .xls sample (Sample) and a .ppt (model) of what I am looking for.

This is my first time using this forum so I hope nothing stupid comes out.

Luís

• ###### 1. Re: How can I attribute a calculated field aggregation to different dates?

Hi Luis,

Welcome to the forums!

I have created a workbook showing what you described in your PPT sample (the figures do not match though, hopefully they weren't supposed to) using your SAMPLE Excel file.

Hopefully this gets you started with what you need.

You should be able to add dimensions like SG_STAE to the filters and get the effect you're after.

Cheers!

Mark

• ###### 2. Re: How can I attribute a calculated field aggregation to different dates?

Thanks Mark,

Awsome!

• ###### 3. Re: How can I attribute a calculated field aggregation to different dates?

Just one quick question,

I actually have information for 13 other 'courses'. The problem is that each one can have a different graduation time. This means that not always the number of years would be 5.

How would you solve this?

Thanks again

• ###### 4. Re: How can I attribute a calculated field aggregation to different dates?

Thought about it here and a parameter would be better..

Cant test it now because I only have TB at work..

• ###### 5. Re: How can I attribute a calculated field aggregation to different dates?

I think your parameter approach would work nicely--you could set it as an integer as "# of years to typical graduation." I went ahead and stuck it in the attached workbook.

That way, you can use it in both the [Year Label], and also the [X-year Graduates] calculated field.

Note that you may want to add one or subtract 1 to the "years offset" in both of these calculated fields. (I have left it as subtracting 1), so I might be grabbing graduates for the wrong year--again, check the table on Sheet 1 to verify...

The biggest nuisance is that since the Year Label is a text string, it re-orders bizarrely... I had handled it by adding a custom sort on the Year Label field in the actual Dimensions pane. (I left the 8-year parameter option off so you could see what it does when not treated.)

1 of 1 people found this helpful
• ###### 6. Re: How can I attribute a calculated field aggregation to different dates?

The most important thing is that now I "got" the ideia of using the calculated table to assit in constructing other views.

I am creating a complementary dashboard for the post-secondary education data and I got stuck with an apparently simple problem. (Maybe I should have created another topic for this).

I need to inform the variables that I am using (e.g., admitted and graduated students) by 10k inhabitants by state or municipality (I made one cloropeth map for each). The number of inhabitants (i.e., population) data refers the municipalities population. My problem is that there are municipalities with 0 registers for any given variable. This means that for municipalities a simple calculation (variable/population) will solve my problem. But when I do the same calculation for the states, the municipalities with 0 registers will, obviously, not show in the calculation. With insight from your previous responses, the best way to solve this, I think, would be to do a table calculation with state and populations as column. What I don’t know is how not to ‘count more than once’ the population of the municipalities that have more than one course (consequentially, more than one count for the variable).

Attached an xls better explaining the problem.

Thanks again for helping me, it’s been insanely useful!