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)

[Anonymous User]

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.

[JasmineP]

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.

[Anonymous User]

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

[JasmineP]

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

[Anonymous User]

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

[JasmineP]

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

[Anonymous User]

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

[JasmineP]

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

[Anonymous User]

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