topic Re: Custom BDS - Aggregation Calculation- VBScript for Median Home Owner Value in ArcGIS Solutions Questions

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

Anonymous User — Wed, 13 Feb 2013 17:09:26 GMT

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)

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

Anonymous User — Wed, 13 Feb 2013 23:28:14 GMT

Original User: usiel

Jasmine,

Thats a pretty advanced question let me check see if I can get someone on the data/dev team to take a look at this.

Regards,
Jason R.

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

JasmineP — Wed, 13 Feb 2013 23:35:42 GMT

Ok, thanks. I might be over-thinking the whole thing. When I create the Custom BDS layer, there's a "median" option for Aggregation, although I'm not sure if it's the same methodology that was used to aggregate the Median Home Owner Value variable in BA 10.0.

I really appreciate you checking this out for me.

Thank you!

Jasmine,

Thats a pretty advanced question let me check see if I can get someone on the data/dev team to take a look at this.

Regards,
Jason R.

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

Anonymous User — Fri, 15 Feb 2013 14:16:41 GMT

Original User: jhincy

Jasmine,

Are you looking to create your own Median Home Value calculation, customize our calculation, or use our calculation unmodified? In our dataset we use 2012 Median Value of Owner Occupied Housing Units(MEDVAL_CY) and its a standard variable in our base dataset.

Here is the calculation directly from our 2012 dataset. The stub names in the calculation are the same as the 2011 dataset.

<Summarization name="MEDVAL_CY" FieldName="MEDVAL_CY" dataset="2012/2017 Demographics (Esri)" aliasname="2012 Median Home Value" basetype="" SummaryType="CALC" WeightField="" category="2012 Home Value (Esri)" previewvalue="283479" longdescription="2012 Median Home Value (Esri)" usedfields="VAL0_CY,VAL50K_CY,VAL100K_CY,VAL150K_CY,VAL200K_CY,VAL250K_CY,VAL300K_CY,VAL400K_CY,VAL500K_CY,VAL750K_CY,VAL1M_CY" indexbase="167749">Dim values(10)%nl% values(0) = GetValue(&quot;VAL0_CY&quot;)%nl% values(1) = GetValue(&quot;VAL50K_CY&quot;)%nl% values(2) = GetValue(&quot;VAL100K_CY&quot;)%nl% values(3) = GetValue(&quot;VAL150K_CY&quot;)%nl% values(4) = GetValue(&quot;VAL200K_CY&quot;)%nl% values(5) = GetValue(&quot;VAL250K_CY&quot;)%nl% values(6) = GetValue(&quot;VAL300K_CY&quot;)%nl% values(7) = GetValue(&quot;VAL400K_CY&quot;)%nl% values(8) = GetValue(&quot;VAL500K_CY&quot;)%nl% values(9) = GetValue(&quot;VAL750K_CY&quot;)%nl% values(10) = GetValue(&quot;VAL1M_CY&quot;)%nl% %nl% Dim rangeValues(10,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 = CalculateMedianLinear(values, rangeValues)%nl% %nl% ReturnValue(median)<Defaults decimals="0" percentage="" valueType="CURRENCY" indexable="TRUE" /></Summarization>

Regards,

Jeff Hincy

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

JasmineP — Fri, 15 Feb 2013 16:54:41 GMT

Hi Jeff,

I want to use your calculation unmodified. I'll give this one a try and then let you know how it works.

Thanks so much for your help. I really appreciate it!

Jasmine

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

Anonymous User — Fri, 15 Feb 2013 17:02:03 GMT

Original User: jhincy

Jasmine,

Your welcome! If you are using our variable then just make sure its in your current BDS layer and if not you can use the Custom Data setup wizard to add it and all the child variables required to calculate it (this is done automatically when you add the median) back to the BDS layer, and then you just select that variable to add it to a report or query it using any of the BA wizards. You will not need to build the calculation anywhere in the application after that.

Regards,

Jeff

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

JasmineP — Fri, 15 Feb 2013 19:00:10 GMT

Hi Jeff,

Unfortunately this code didn't work. In the Custom BDS wizard, I copied your code into the custom calculation window and when I verified the code I rec'd the attached error message.

I have a Basic license for BA which is probably why this variable isn't included in my dataset. I ran a census tract spatial overlay to grab the 2012 home owner variables from BAO. Now that I have all the 2012 home owner value variables, I've created a custom BDS layer which works great (except for the median calculation for home value).

Any ideas or suggestions? Thanks again for your help.

Jasmine

Jasmine,

Are you looking to create your own Median Home Value calculation, customize our calculation, or use our calculation unmodified? In our dataset we use 2012 Median Value of Owner Occupied Housing Units(MEDVAL_CY) and its a standard variable in our base dataset.

Here is the calculation directly from our 2012 dataset. The stub names in the calculation are the same as the 2011 dataset.

<Summarization name="MEDVAL_CY" FieldName="MEDVAL_CY" dataset="2012/2017 Demographics (Esri)" aliasname="2012 Median Home Value" basetype="" SummaryType="CALC" WeightField="" category="2012 Home Value (Esri)" previewvalue="283479" longdescription="2012 Median Home Value (Esri)" usedfields="VAL0_CY,VAL50K_CY,VAL100K_CY,VAL150K_CY,VAL200K_CY,VAL250K_CY,VAL300K_CY,VAL400K_CY,VAL500K_CY,VAL750K_CY,VAL1M_CY" indexbase="167749">Dim values(10)%nl% values(0) = GetValue(&quot;VAL0_CY&quot;)%nl% values(1) = GetValue(&quot;VAL50K_CY&quot;)%nl% values(2) = GetValue(&quot;VAL100K_CY&quot;)%nl% values(3) = GetValue(&quot;VAL150K_CY&quot;)%nl% values(4) = GetValue(&quot;VAL200K_CY&quot;)%nl% values(5) = GetValue(&quot;VAL250K_CY&quot;)%nl% values(6) = GetValue(&quot;VAL300K_CY&quot;)%nl% values(7) = GetValue(&quot;VAL400K_CY&quot;)%nl% values(8) = GetValue(&quot;VAL500K_CY&quot;)%nl% values(9) = GetValue(&quot;VAL750K_CY&quot;)%nl% values(10) = GetValue(&quot;VAL1M_CY&quot;)%nl% %nl% Dim rangeValues(10,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 = CalculateMedianLinear(values, rangeValues)%nl% %nl% ReturnValue(median)<Defaults decimals="0" percentage="" valueType="CURRENCY" indexable="TRUE" /></Summarization>

Regards,

Jeff Hincy

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

Anonymous User — Fri, 15 Feb 2013 19:58:03 GMT

Original User: jhincy

Jasmine,

Sorry if I wasnt clear enough but you dont need that calculation. I added it in case you wanted to modify it but its not formatted 100% for use in our field calculator and would take some tweaking.

Instead go to Business Analyst->Custom Data Setup->Open and Edit and existing BDS layer and select your BDS layer you wish to add the variable to.

When you are in the Variable selection dialogue navigate to the Business Analyst bds layer and select the Median Value of Owner Occupied Housing Units and move it from the left dialogue to the right dialogue. At this point a popup screen will appear listing the base variables needed to calculate the median for home value. Just hit yes to add them also and then hit next and hit Finish and save your BDS.

The calculation for that median will be added to your BDS layer behind the scenes and will be available in the Business Analyst wizards when that BDS is loaded into your table of contents.

If that still isnt working I suggest you call Tech Support so they can walk you through the process.

Regards,

Jeff

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

JasmineP — Fri, 15 Feb 2013 20:17:26 GMT

Hi Jeff,

The problem is, my Business Analyst BDS layer does not include any 2012 variables or Home Owner Value variables, whatsoever. That's why I'm creating this custom BDS.

I am very familiar with BA and creating Custom BDS layers. I don't need any step-by-step instructions, just the original Esri calculation used for aggregating Median Home Owner Value. All I want to do is plug the code into the Edit Calculate Field dialogue box (see attachment) so when I run reports, Median Home Owner Value will be aggregated accurately.

Thank you,
Jasmine

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

Anonymous User — Fri, 15 Feb 2013 20:39:35 GMT

Original User: jhincy

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

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

JasmineP — Fri, 15 Feb 2013 21:21:34 GMT

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

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

Anonymous User — Tue, 19 Feb 2013 13:29:40 GMT

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

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

JasmineP — Tue, 19 Feb 2013 14:26:09 GMT

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.

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

Anonymous User — Tue, 19 Feb 2013 16:40:36 GMT

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)

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

JasmineP — Tue, 19 Feb 2013 19:10:31 GMT

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)