Custom BDS - Aggregation Calculation- VBScript for Median Home Owner Value

[Anonymous User]

Original User: polsonj@interdent.com
Original Date: 2013-02-13T11:09:26-0600

I've created a custom BDS layer from data I imported and everything is working as it should. My only problem is the aggregation of Median Home Owner Value.

I need to create a Custom Calculation in VBScript that will aggregate the Median Home Owner Value data, similar to how Median Household Income (which is included in the original BA dataset) is aggregated. Normally, I would grab the calculation straight from Median Household Income and customize it to fit my needs, but I'm unable to view its custom calculation.

I have provided the code I was trying to use. It might be completely wrong but hopefully it will give an idea of what I'm trying to do. Also, I've attached a screenshot of what I'm doing, and the VBScript error I'm receiving.

I really appreciate any help I can get. Thank you!!!

Dim values(11)%nl% values(0) = Round([VAL0_CY])%nl% values(1) = Round([VAL50K_CY])%nl% values(2) = Round([VAL100K_CY])%nl% values(3) = Round([VAL150K_CY])%nl% values(4) = Round([VAL200K_CY])%nl% values(5) = Round([VAL250K_CY])%nl% values(6) = Round([VAL300K_CY])%nl% values(7) = Round([VAL400K_CY])%nl% values(8) = Round([VAL500K_CY])%nl% values(9) = Round([VAL750K_CY])%nl% values(10) = Round([VAL1M_CY])%nl%%nl% Dim rangeValues(11,1)%nl% rangeValues(0, 0) = 0%nl% rangeValues(0, 1) = 50000%nl% rangeValues(1, 0) = 50000%nl% rangeValues(1, 1) = 100000%nl% rangeValues(2, 0) = 100000%nl% rangeValues(2, 1) = 150000%nl% rangeValues(3, 0) = 150000%nl% rangeValues(3, 1) = 200000%nl% rangeValues(4, 0) = 200000%nl% rangeValues(4, 1) = 250000%nl% rangeValues(5, 0) = 250000%nl% rangeValues(5, 1) = 300000%nl% rangeValues(6, 0) = 300000%nl% rangeValues(6, 1) = 400000%nl% rangeValues(7, 0) = 400000%nl% rangeValues(7, 1) = 500000%nl% rangeValues(8, 0) = 500000%nl% rangeValues(8, 1) = 750000%nl% rangeValues(9, 0) = 750000%nl% rangeValues(9, 1) = 1000000%nl% rangeValues(10, 0) = 1000000%nl% rangeValues(10, 1) = 1000002%nl% %nl% Dim median%nl% median = CalculateMedianPareto(values, rangeValues)%nl% %nl% ReturnValue(median)

[JasmineP]

Ok--- Lets just say I have a custom variable in the Census 2010 geography that is Median Car Value. With Median Car Value, there are also five base variables: Car Value <$5000, Car Value $5,001-10,000, Car Value $10,001-20,000 and so on.

Say I wanted Median Car Value to be calculated and aggregated the same way that Median Home Value is aggregated in the original Business Analyst bds layer. How would I do this?

I previously worked with Matt P. from tech support and he said that my dataset doesn't include Home Owner Value variables or Median Home Value. To get the Home Owner Value variables, he instructed me to run a spatial overlay using the BA Census Tract layer (which I did) from BAO. The only step he forgot to mention was how to setup Median Home Value so that it's aggregated in the same methodology used in the original BA bds. Unfortunately, my maintenance is no longer current so I can't call tech support.
Here is my correspondance with Matt:

Hi Jasmine,

Nice talking to you. As discussed please use the export map function > Choose pdf as the type (it is more reliable). As for the online data and reports there will hopefully be updated reports for those reports later in November. We talked about a process of creating a new custom report by appending, here is the rough workflow:
1. Right click the census block group > Properties > Turn off all the fields except ones that are necessary for your (Geographic Identifiers (ID) > Select all > Export to a shapefile or feature class.
2. BA > Analysis > Append data > Use Online Data > Append to the newly exported census block layer 3. Search and add all the variables that were used in the executive summary and housing profile > Search and click the arrow to add them. Next and finish.
4. Once the data is appended. (may take a while) 5. BA > Custom data setup > Import your own data > Use base geography > Add your appended layer and choose the ID (Cenus ID) to identify.
6. Add your variables and finish.
7. Create a report > Using the report designer > Edit > Add a bds > Add the newly created bds that you made > add those variables.

Please let me know if you have any additional questions. Thanks Jasmine!

Regards,

Matthew P.

Jasmine, 

Understood, but if you are you using the BA Custom Data Setup wizard and if you have the Business Analyst Data loaded into your MXD then you will have access to whichever dataset you are licensed for and it would not matter if you created a BDS on our data or imported your own data. Either way you typically would be required to join your data to our geometries and as part of that process you would have access to our entire dataset in the variable select dialogue. 

So the only way you wouldnt see the BA data is if you imported your data and you used your own geometries. Either way if you are not seeing the BA variables then I suggest you call in an incident to tech support and if they cannot assist you can tell them to get me involved and we can work through it on the phone. 

I hope this helped. 🙂 

Regards, 

Jeff

[Anonymous User]

Original User: jhincy

Jasmine,

Ok let me see if I can help you here then.  First, can you let me know what dataset you have?  Is it BA Desktop 2006/2011 or is it earlier?

Go to Business Analyst-Preferences and under the general tab and look at the very bottom and tell me the Build # and then go to the Dataset tab and tell me what Current Dataset you have selected.

Thanks

Jeff

[JasmineP]

Build #10.1.0.85
Current dataset: USA (ESRI)

The dataset I have is 2011/2016. It also includes 2010 variables from the most recent Census.

[Anonymous User · Accepted solution]

Original User: jhincy

OK, below is the calculation that is formatted to work in the Custom Data Setup Wizard you will need to calculate the Current Year Median Home Value. Just be sure you have all the underlying home value variables used in the calculation in your BDS or it wont work.  I just manually built this calculation and I havent tested it so I would suggest if it works that you create a trade area and compare the online Median Home Value to this one to be sure I didnt make any mistakes.  Keep in mind if you pulled the data from BAO the Median Home Value data will be 2012 vintage and not 2011.

As an aside if you update your maintenance for the 2012 data desktop release this variable will be included in that dataset.

Let me know if this works for you.

Regards,

Jeff Hincy


Dim values(10)
values(0) = Round(GetValue("VAL0_CY"))
values(1) = Round(GetValue("VAL50K_CY"))
values(2) = Round(GetValue("VAL100K_CY"))
values(3) = Round(GetValue("VAL150K_CY"))
values(4) = Round(GetValue("VAL200K_CY"))
values(5) = Round(GetValue("VAL250K_CY"))
values(6) = Round(GetValue("VAL300K_CY"))
values(7) = Round(GetValue("VAL400K_CY"))
values(8) = Round(GetValue("VAL500K_CY"))
values(9) = Round(GetValue("VAL750K_CY"))
values(10) = Round(GetValue("VAL1M_CY"))

Dim rangeValues(10,1)
rangeValues(0, 0) = 0
rangeValues(0, 1) = 50000
rangeValues(1, 0) = 50000
rangeValues(1, 1) = 100000
rangeValues(2, 0) = 100000
rangeValues(2, 1) = 150000
rangeValues(3, 0) = 150000
rangeValues(3, 1) = 200000
rangeValues(4, 0) = 200000
rangeValues(4, 1) = 250000
rangeValues(5, 0) = 250000
rangeValues(5, 1) = 300000
rangeValues(6, 0) = 300000
rangeValues(6, 1) = 400000
rangeValues(7, 0) = 400000
rangeValues(7, 1) = 500000
rangeValues(8, 0) = 500000
rangeValues(8, 1) = 750000
rangeValues(9, 0) = 750000
rangeValues(9, 1) = 1000000
rangeValues(10, 0) = 1000000
rangeValues(10, 1) = 1000002

Dim median
median = CalculateMedianLinear(values, rangeValues)

ReturnValue(median)

[JasmineP]

Hi Jeff,

This latest code you provided worked. Thank you soooooo much for all your help, I really appreciate it!!

Best Regards,
Jasmine

OK, below is the calculation that is formatted to work in the Custom Data Setup Wizard you will need to calculate the Current Year Median Home Value. Just be sure you have all the underlying home value variables used in the calculation in your BDS or it wont work.  I just manually built this calculation and I havent tested it so I would suggest if it works that you create a trade area and compare the online Median Home Value to this one to be sure I didnt make any mistakes.  Keep in mind if you pulled the data from BAO the Median Home Value data will be 2012 vintage and not 2011.

As an aside if you update your maintenance for the 2012 data desktop release this variable will be included in that dataset.

Let me know if this works for you.

Regards,

Jeff Hincy


Dim values(10)
values(0) = Round(GetValue("VAL0_CY"))
values(1) = Round(GetValue("VAL50K_CY"))
values(2) = Round(GetValue("VAL100K_CY"))
values(3) = Round(GetValue("VAL150K_CY"))
values(4) = Round(GetValue("VAL200K_CY"))
values(5) = Round(GetValue("VAL250K_CY"))
values(6) = Round(GetValue("VAL300K_CY"))
values(7) = Round(GetValue("VAL400K_CY"))
values(8) = Round(GetValue("VAL500K_CY"))
values(9) = Round(GetValue("VAL750K_CY"))
values(10) = Round(GetValue("VAL1M_CY"))

Dim rangeValues(10,1)
rangeValues(0, 0) = 0
rangeValues(0, 1) = 50000
rangeValues(1, 0) = 50000
rangeValues(1, 1) = 100000
rangeValues(2, 0) = 100000
rangeValues(2, 1) = 150000
rangeValues(3, 0) = 150000
rangeValues(3, 1) = 200000
rangeValues(4, 0) = 200000
rangeValues(4, 1) = 250000
rangeValues(5, 0) = 250000
rangeValues(5, 1) = 300000
rangeValues(6, 0) = 300000
rangeValues(6, 1) = 400000
rangeValues(7, 0) = 400000
rangeValues(7, 1) = 500000
rangeValues(8, 0) = 500000
rangeValues(8, 1) = 750000
rangeValues(9, 0) = 750000
rangeValues(9, 1) = 1000000
rangeValues(10, 0) = 1000000
rangeValues(10, 1) = 1000002

Dim median
median = CalculateMedianLinear(values, rangeValues)

ReturnValue(median)