Indicators Tutorial

Prerequisites

The following prerequisites should be met before starting this tutorial:

• This tutorial is a continuation of the TQL Tutorial. If you have not yet followed the tutorials related to those subjects please do so first.
• This tutorial can be followed with any project of any arbitrary location. There must be WRITE access to the Project. Recommended is to create or load a project in the editor with 3 or more neighborhoods at least partially within the project area.
• Microsoft Excel is required for completing this tutorial. 

Preparations

Take the following steps as preparation for following this tutorial:

• Start your project. This can be a pre-existing project, or a newly created project.
• Start Microsoft Excel. Excel will be required throughout the tutorial.

Principles of Indicators

Indicators are statistical, numerical calculation models. This is in contrast to Grid Overlays, which are geographical calculation models. Where Overlays show results geopgraphically, Indicators aggregate such results into singular numbers.

The default Green Indicator's results in table-form.

Indicators present in a Project are recalculated during each calculation cycle.

Each Indicator is defined by an Excel file. In such an Excel file, specific cells are assigned to have data written into them from the Session. Other cells containing Excel formula's can then perform calculations based on that data. Finally, specifically marked cells will contain results, which are then output back to the Session.

Which data is to be included in an Excel file for an Indicator is indicated through TQL statements. These form specific requests for data from the Session. A cell which has a valid TQL statement as a name will have the text of number resulting from that statement as its value.

To start off, an Excel for an Indicator can be made using specific TQL statements, i.e. statements which reference specific Items, with one query per Item. However, for the sake of flexibility and scalability, for Excels there are syntaxes available which allow for a more dynamic way to reference Items based on their availability in the Project.

The output of an Indicator can be defined with HTML, CSS and JavaScript. The most common form is a table-styled output, although normal text, diagrams, charts, and other form of representation are also possible.

In summary, the processing of an Indicator is as follows:

• Its required TQL queries are initialized
• The cells with TQL SELECT statements are filled
• The formulas in the Excel of the Indicator are run
• Cells with TQL UPDATE statements, EXPLANATION, or SCORE names, have their values read out.
• Items affected by TQL UPDATE statements have their relevant Attributes updated.
• The Indicator Panel in the Client application shows the substantive explanation and score.

[[File:Indicator tutorial 00 02 flow diagram.jpg|frame|center|For any Excel in a calculation cycle, the flow of data into and out of the Excel.]

During this tutorial, there will be mentions of using specific cells. The exact cells used are only for consistency and legibility during the tutorials. When applying the techniques described in this tutorial, any cell(s) can be used.

Creating a simple Indicator Excel

As a first exercise, a simple Excel for an Indicator will be created which will touch on the essential outputs and how they are visualized in the Tygron Platform.

Create a new Excel file, and save it locally to your computer. Ensure it is located in a place where you are able to find it later.

A new Excel file.

Open the Excel file in Excel.

Select cell B1. Notice at the top of the screen, to the left of the formula bar, the coordinate of the cell is listed.

A selected cell has an address, displayed in the Name input area.

Enlarge the address field by dragging the edge with the three dots to the right.

The Name input area can be made larger for easier reading and editing.

Click in the address field, and enter the following text:

EXPLANATION

Ensure the letters match exactly. Hit enter on the keyboard to confirm the name of the cell.

The cell B2 is being named EXPLANATION.

In the formula field, the content of the cell can be entered. Enter the following text:

This is an example Indicator

A named cell, with content for that cell being added.

Select cell B2.

Again, click in the address field. Enter the following text:

SCORE

Ensure the letters match exactly. Hit enter on the keyboard to confirm the name of the cell.

A second named cell. This one is named SCORE.

In the formula field, enter the following value:

0,8

Ensure it is entered such that it is interpreted by Microsoft Excel as a number.

The value of 0,8 being entered as the value for the SCORE cell.

Note that depending on your application's localization, the decimal seperator may either be a comma (" , ") or a point (" . ").

Save the Excel file, and close Microsoft Excel.

In the editor, go to:

Editor → Current Situation (Ribbon tab) → Indicators (Ribbon bar) → Empty Indicator (Dropdown)

Adding a new empty Indicator.

The left panel is now open with a list of all Indicators in the Project. The newly added Indicator is already listed.

The new Indicator is listed.

The top bar has also appeared (if it was not displayed already), listing the newly added Indicator.

The Top Bar shows the Indicator.

In the right panel, change the "name" of the Indicator to "Example indicator", and the "short name" to "Example".

Renaming the Indicator

In the right panel, find the "Excel" input area. Notice a default Excel file is already set, named "indicator.xlsx".

The Excel of the Indicator. The default indicator.xlsx is currently linked.

Click on "Select Excelsheet".

Click Select Excelsheet to manage this Indicatotor's Excel.

This opens the "Excel selection" window.

The Excel selection window allows for managing all Excels currently in the Project.

This window lists all the Excel files currently present as assets in the Project. This includes Excel files which are available by default in any Project, but also files specifically uploaded to be included as assets in a Project.

At the bottom of the window, click on "Add local File".

Click Add local File to add a file from the local computer.

Select the created Excel file, and confirm to upload it to the Project.

Select the newly created Excel file on the local computer.

The Excel file, created locally, is now uploaded as an asset to the Project, and can be used as the underlying model for Indicators.

Ensure the newly uploaded Excel is selected.

The new Excel file is uploaded.

Then, click on "Select" on the bottom right of the window to select the Excel file for the Indicator.

Click on "Select" to confirm the selected Excel to be linked to the Indicator.

The "Excel selection" window will now close, and the created Excel file is now listed in the right panel.

The new Excel file is now linked to the Indicator.

A recalculation is required for Indicators to generate and present output.

At the top left of the editor screen, hover over the recalculation icon. In the dropdown that appears, click on "Update".

Click on Update to recalculate all models in the Project, Indicators included.

When the calculation has completed, click on the Indicator in the top bar.

The top bar shows the Indicator and its "calculated" score.

This opens the Indicator panel.

The Indicator panel's output as defined by the EXPLANATION cell, and displaying a score as defined by the SCORE cell.

Notice the following elements in the interface:

• The Indicator panel displays the full name of the Indicator.
• The Indicator panel displays the text entered in the EXPLANATION cell.
• The Indicator panel displays a score percentage of 80%, matching the "0,8" value for the score.
• The top bar displays the short name of the Indicator, due to the potentially limited space for display.
• The top bar displays a score bar, which graphically shows a score of 80% due to the bar being 80% filled.

Effective use of these outputs relies on matching the intent of the outputs with the information provided.

• The score of the Indicator should be a computed net-result of the particular theme the Indicator describes.
• The explanation should be a clarification on what that score is composed of, and what the various sub-scores are (if applicable).

Downloading Excel files

When an Excel file is uploaded to a Project, the exact file as it was uploaded is present in the Project as an Asset. This is a file or other similar data which is available in the Project, and may be referenced and used by other Items.

Such Assets can be downloaded from a Project as well.

In the editor, go to:

Editor → Current Situation (Ribbon tab) → Indicators (Ribbon bar) → Example indicator (Left panel)

In the right panel, find the "Excel" input area.

Click on "Select Excelsheet" to reopen the "Excel selection" window.

Click on Select Excelsheet.

The Excel file currently used in the Indicator is highlighted.

The Excel selection window, still showing the uploaded Excel.

In the highlighted entry row, click on the "Get" icon.

The Get option allows for downloading assets (such as Excels) from the Project.

Use the file save screen to save the file to a location on your computer. Remember the location where you save the file.

When the download has completed, open the file in Microsoft Excel. You will see that it is the same file previously created.

The re-opened Excel file, with the same content as the original was uploaded with.

This allows you to obtain Excel files from a Project for editing and updating. Any Excel file can be downloaded, modified, and then uploaded again as an update of the pre-existing Asset, or as a new file separate from the original.

Adding data to Indicators

Expanding on the first exercise, the Excel file can be expanded to obtain data from the Session it is loaded into. That information can then be displayed verbatim in its output, or used for or preprocessed by additional calculations.

For this part of the tutorial, the intent will be to set up a calculation to determine which amount of land in the Project Area is actually in use by built features (Buildings, roads, trees, etc.)

Reopen Microsoft Excel, and reopen the local Excel file.

The most important property of indicators is the ability to obtain data from a Session and use it in calculations and finally the output. To obtain data from a Session, TQL queries are used.

In the editor, open the Query tool.

Editor → Current Situation (Ribbon tab) → Queries (Ribbon bar)

Opening the query tool.

Create a query of the following form:

SELECT_LANDSIZE_WHERE_

A simple query for obtaining the total area of the Project.

Copy this TQL query by selecting it, and pressing ctrl-C on the keyboard, or by right-clicking and then selecting the "Copy" option.

In the Excel, select cell B4, and for the name of the cell paste the copied query.

A cell named with a TQL query.

This has now named this cell, and the name is a valid TQL statement. This allows the Tygron Platform to recognize that this particular cell should be filled with data from the running session.

As value for this cell, enter the following value:

100

A cell named with a TQL query and a palceholder value.

While the cell is marked for the Tygron Platform to automatically fill with a value from the running Session, it is helpful to enter temporary data into such a cell as well. This temporary or "placeholder" data will be used by formulas in the Excel file while it is opened in Microsoft Excel, and allow you to see how calculations will function.

Once the Excel is uploaded and recalculated by the Tygron Platform, data is inserted into the named cells (named with a TQL SELECT statement). If there is placeholder data in those cells already, that placeholder data is overwritten with data from the Session.

Return to the query tool, and create a query of the following form:

SELECT_LOTSIZE_WHERE_

A query to get the total footprint of all built features in the Project.

Copy this TQL query by selecting it, and pressing ctrl-C on the keyboard, or by right-clicking and then selecting the "Copy" option.

In the Excel, select cell B5, and for the name of the cell paste the copied query.

A second cell with a TQL name.

As value for this cell, enter the following value:

50

The second TQL-named cell now also has a placeholder value.

Finally, select the "EXPLANATION" cell again (B1). Change the value of the cell to the following formula:

="Built area: "&B5&" of "&B4&" = "&ROUND( B5 / B4 ; 2 )

A formula referencing the named cells, to display their values and calculate a fraction.

Note that depending on your application's localization, the ROUND function (or any function):

• may have a translated function name.
• may have an argument separator be either a semicolon (" ; ") or a comma (" , ").

Now, it can be clearly seen that if the input cells are filled with proper values, then the EXPLANATION cell will be filled with computed values as well.

In the editor, go to:

Editor → Current Situation (Ribbon tab) → Indicators (Ribbon bar) → Example indicator (Left panel)

Select the Indicator in the left panel.

In the right panel, find the "Excel" input area.

Click on Select Excelsheet.

Click on "Select Excelsheet" to reopen the "Excel selection" window.

The Excel selection window.

The Excel file currently used in the Indicator is highlighted.

In the highlighted entry row, click on the "update" icon.

The Update icon allows for updating an existing asset in-place with a new version.

This will reopen the file selection screen.

Select the local Excel file, and confirm to upload it to the Project.

The Excel file, which has been updated locally, is now uploaded as an asset to the Project. Specifically, the previously uploaded Excel file has now been replaced with a new version of the Excel file. And any Indicator (or other Item in the Project) referencing the Excel file will now reference the updated file.

Ensure the newly uploaded Excel is selected, and click on "Select" on the bottom right of the window to confirm it.

Confirm the selection.

Recalculate the Project if neccesary.

Update to recalculate all models in the Project.

Click on the Indicator in the top bar to open the indicator panel.

The contents of the Indicator Panel now show actual values from the Project.

Notice the indicator now displays:

• The amount of land area, and the amount of area in use for Buildings.
• The fraction of land area now in use for Buildings.
• Values which are not the placeholder values. They are overwritten with the live Session data.

This Indicator is made such that the inputs and calculations change when the data in the Project changes.

Debugging Excel files

When an Excel file is uploaded to a Project, the original file is present as an Asset in the Project. However, when a calculation is performed the Tygron Platform will fill in such an Excel file based on the TQL statements used, and then have all the formulas in the sheet update their results in turn.

As you create more Excel files for calculations, and those files may grow in complexity, there will come a point where the result of the calculation will be unexpected. It may be a value which is not immediately explainable, or an error which blocks the parsing of the Excel file altogether. In these cases, it is good to be able to see exactly how the Tygron Platform has filled in and calculated the Excel file.

Items such as Indicators have a "debug" option, which allows a download of the filled-in Excel file.

In the editor, go to:

Editor → Current Situation (Ribbon tab) → Indicators (Ribbon bar) → Example indicator (Left panel)

In the right panel, find the "Excel" input area.

File:Indicator tutorial generic select excelsheet with debug.jpg

TODO

Click on "Debug Excelsheet". This will open the file save window.

The debug file can be saved on your local computer.

Use the file save screen to save the file to a location on your computer. Remember the location where you save the file.

When the download has completed, open the Excel file in Microsoft Excel. You will see that this file contains actual values from the Project.

The debug file is the same as uploaded, except the TQL-named cells have as a value the actual results of the TQL statements.

Note that as a matter of good practice, the debug file should not be reuploaded to a Project. This has to do with the undue presence of data in Indicators. This will be further clarified later on.

Debug file is named with a "debug-" prefix, unless renamed. Make sure you know which file is which.

If insight into the debug Excel file leads to a change in the content of the Excel file, update the original file instead. Download and open the "original" Excel file from the Assets overview as done previously, make the desired changes there, and re-upload the file.

Excel name management

(Cell)names in Excel files serve as a named index to specific cells in that Excel file. Rather than referring to a cell purely based on its coordinates, it allows the creators of Excel files and formulas therein to refer to cells by some name which clarifies the intent of the value.

When working with names in Excel files, there are a few facts to be aware of:

• Cells can have multiple names
• Names can refer to multiple cells

For Microsoft Excel, this functionality is fitting. However, for compatibility with the Tygron Platform, the following are prerequisites:

• A single cell may have only one name. Otherwise, the Tygron Platform will be unsure what value to place in such a cell or read from such a cell.
• A name may only refer to a single cell. Otherwise, the Tygron Platform will be unsure what value to obtain from a cell, or in which cell to place a specific (part of) a value.
• A name may not refer to a non-existent cell. Otherwise, there is no value for the Tygron Platform to read or manipulate.

Ensure the original Excel file is opened in Microsoft Excel (not the debug file).

In the ribbon's header click on "Formulas", and then find the "Defined Names" section.

Select formulas (shown top-left), then the Defined Names block (shown far-right).

Click on "Name Manager". This will open the Name Manager.

The Name Manager allowes for editing names of cells in an Excel.

The Name Manager provides an overview of all names present in the Excel file. It also provides the means to edit them, either to change them or to remove them altogether.

It is essential to remember that although the creation of named cells is easily done via the address field as done up to this point, to change them the Name Manager must be used. This is for both when correcting a potential typo in the created name, or when replacing one name for another, or when removing a cell entirely.

Click on the entry for "SELECT_LOTSIZE_WHERE_", and then click on "Edit".

Select the entry to edit, then double-click, or click Edit, to edit it.

In the prompts that appears, change the name to the following:

SELECT_FLOORSIZE_WHERE_

The entire name can be edited freely in this prompt.

Confirm the name change, close the Name Manager, and click on cell B5.

The cell has been renamed to a different valid TQL query.

Notice that the name of the cell has now changed.

Changes in Project state

The strength of an Excel-driven Indicator is that it allows for the automatic recalculation of changing data in a Project. It's possible to quicky set up a demonstration of this effect.

Find an open spot in the 3D world where an additional building can be added. (For optimal effect, ensure it's a place where there is no Building of any kind, including pavement of grass fields.)

Example of a location which is entirely empty.

Add a new Measure.

Editor → Future Design (Ribbon tab) → Measures (Ribbon bar) → Add Empty Measure (Dropdown)

Add a new empty measure.

The new Measure is now added and selected in the left panel, and its details are visible in the right panel.

A new Measure is available.

At the bottom of the left panel, click on "Add Building" to add a Building to the Measure.

Opt to add a Building to the Measure.

The Building is added to the Measure, and its details are visible in the right panel.

The added Building has some default properties.

Click on "Draw Area".

The Draw Area button.

Draw a selection for the Building in the 3D Visualization, and click on "Apply Selection".

A drawn selection. When ready, click on Apply Selection.

Optionally, change the number of floors for the Building, or change to the Function of the Building to any kind of highrise.

Select the Measure itself in the left panel, and in the right panel click on "Activate Measure".

The Activate Measure option in the right panel.

This will start a test run, and add the Building to the 3D Visualization.

The Building is added to the Project area.

Click on the Indicator in the top bar, to open the indicator panel.

Notice that the result in the Indicator has changed, to reflect the changed situation in the 3D Visualization.

The changed results in the Indicator.

On the left side of the ribbon, click on the "Stop" button of the test run to stop the test run.

Click the Stop button to stop the testrun.

This will restore the 3D Visualization to its original state.

The Project Area has been restored.

Notice that the Indicator's results are also restored.

The Indicator results are restored.

An Indicator will by default always read out the "most future" situation. If the Session is in editing mode, this is always the Current Situation. During a Test run, this is the Future Design. It is also possible to obtain values specifically from the Current or Maquette state of the Session, to allow for more expansive displays of data or comparisons in situations.

Assignments

1. Upload the Excel with the changed cell name, and inspect the results.
2. Change the Excel to use a combination of a LOTSIZE query and a FLOORSIZE query to compute the average amount of floors of Buildings.
   • Change the text of the output accordingly.
   • Where necessary, check whether your formula is going to divide by 0, and change your output accordingly.
   • (As a pointer for average amount of floors, the LOTSIZE times the amount of FLOORS equals the FLOORSIZE)
   • (As a pointer for error checking, consider using an IF() function, or an IFERROR() function)
3. Upload the Excel again and inspect the results.

Indicator Template and results per location

Excel files for Indicators can easily become rather complex. Depending on the desired data or calculations, a lot of information should be read out from a Project, and more cells need to be used to perform calculations or obtain results.

In addition, styling for the output of Indicators can be done in HTML. This allows for a great amount of flexibility and options for styling, as the output of an Indicator can effectively be a miniature web page. However, HTML may require specific knowledge to implement effectively.

To make it easier to develop Indicator Excels, a template file is available which contains both organisational styling as well as some ready-made HTML for a table display of results.

Download the indicator template Excel file:

https://support.tygron.com/wiki/File:indicator_template.xlsx

Open the downloaded Excel file.

Template excel structure

The template file consists of 3 sections. The sections are mostly only differentiated by styling. Styling has no effect on the calculations, output, or function of the Excel file. It is entirely for the purpose of human overview, so that at a glance it is possible to see what operations occur where.

TODO

Inputs The first section of the template consists of a number of dark-grey columns. In these columns, data from the running session can be obtained.

Calculations The second section of the template consists of a number of light-blue columns. These columns are intended for performing calculations. This can include checks whether certain data is present, calculations to determine specific values or results based on the data, and the calculation of score metrics.

Output The last section is for the formatting of output. It comes with a predefined structure for an HTML table. All that needs to be done is for the values, which should appear in the indicator panel, are filled in.

Creating a simple calculation for multiple Items

In the first example Excel file, a simple calculation was made based on 2 inputs: the (total) lotsize and the (total) landsize in the (entire) Project. However, generally results are more manageable when they apply to specific subdivisions of a Project, so that some more overview can be established over the state of more specific location. Generally, the Neighborhoods which are included by default in every Project are used as a standard subdivision.

This means that conceptually, a query such as the following:

SELECT_LANDSIZE_WHERE_

can be replaced by the following, which includes a reference to the ID of a specific Neighborhood (specifically the Neighborhood with ID 0):

SELECT_LANDSIZE_WHERE_NEIGHBORHOOD_IS_0

For small and specifically applicable Excels and Indicators, it is possible to use multiples of this query to obtain data for all desired Neighborhoods. However, in cases where there are a large number, or an indeterminate number of Neighborhoods which you intent to consult, it's not feasible to manually create all individual queries.

Instead, a syntax is available which allows for automatically obtaining the data for all relevant Neighborhoods. This syntax is known as an X-Query:

SELECT_LANDSIZE_WHERE_NEIGHBORHOOD_IS_X

This effectively means "for every Neighborhood".

For this part of the tutorial, the conceptual calculation made previously for the entire Project area as a whole will be remade to provide the same insight on a per-Neghborhood level.

In the Indicator template Excel file, opened in Microsoft Excel, select cell D9. This is one of the orange cells just above the dark-grey cells.

TODO

Click in the address field, and enter the following text:

SELECT_NAME_WHERE_NEIGHBORHOOD_IS_X

Ensure the letters match exactly. Hit enter on the keyboard to confirm the name of the cell.

TODO

This query will obtain the name of the Neighborhood, for every Neighborhood. Phrased differently: starting in the next row, a query will be executed for every Neighborhood on each row.

Queries to retrieve data

Select cell E9. Click in the address field, and enter the following text:

SELECT_LOTSIZE_WHERE_NEIGHBORHOOD_IS_X

Ensure the letters match exactly. Hit enter on the keyboard to confirm the name of the cell.

TODO

Select cell F9. Click in the address field, and enter the following text:

SELECT_LANDSIZE_WHERE_NEIGHBORHOOD_IS_X

Ensure the letters match exactly. Hit enter on the keyboard to confirm the name of the cell.

TODO

These queries will obtain the same data obtained in the previous, simpler example. However, now the results will be per Neighborhood, and the name of the Neighborhood will be included as well.

For legibility, enter the texts "Name", "Lotsize", and "Landsize" as values in cells D7, E7, and F7 respectively.

TODO

Calculations

Next, the desired calculations can be defined.

Select cell L10. This is the first cell in the rows and columns of light-blue cells.

Enter the following formula:

=IF(F10=0;0;E10/F10)

Hit enter on the keyboard to confirm the value of the cell.

TODO

This formula will see if there is a landsize defined. If there is (or more specifically, if the landsize is not zero), then the lotsize is divided by the landsize. This is the same calculation which happened in the earlier example. However, if the landsize is 0, then the result in this cell will be 0 as well. This prevents errors caused by dividing by 0.

For legibility, enter the text "Built fraction" in cell L7.

TODO

Select cell L10 again.

The selection boundary around the cell has a small square in the lower right corner that can be used to drag and expand the formula to other cells as well.

TODO

Click-and-hold the square, and drag it downwards for a large number of rows (up to row 50 should be sufficient).

File:Indicator tutorial 04 10 draqsquare done.jpg

TODO

The formula is now present on every row. Because data will be requested for each Neighborhood per row, the formula will now also calculate per Neighborhood.

TODO

Outputs

Finally, it is possible to configure the output for the Indicator.

Select cell V10. This is the first cell in the first green column, and is surrounded by pre-defined HTML for a table display.

TODO

The value entered or calculated in this cell is shown in this place in the table which will appear in the indicator panel.

Enter the following formula:

=IF(D10="";"";D10)

TODO

This formula means that if D10 is empty, then leave this cell empty as well. This will only happen if, on this row, there is no information about a Neighborhood. However, if there is something in D10, (which should be the name of the Neighborhood), then that value is placed verbatim in this cell for output.

Next, select cell X10. This is the first cell in the second green column. Whatever is entered here will also be shown in the table in the indicator panel, but in the next column.

TODO

Enter the following formula:

=IF(D10="";""; ROUND(L10*100;0) & " %" )

TODO

This formula will again check whether there is a name for a Neighborhood present. If there isn't, the cell is kept empty. But if there is, then the result of the calculation performed is shown here, formatted as a rounded percentage with a percentage sign behind it.

Note that to format the data, the output of the formula in the cell itself is defined. Formatting of text or numbers in the Excel file as done as a visualization option in Microsoft Excel is not carried over to the Tygron Platform.

In cell V7, change the text to "Neighborhood".

In cell X7, change the text to "Percentage built".

In cell Z7, change the text to " " (a single space).

In cell AB7, change the text to " " (a single space).

TODO

Select the range of cells S10 through AF10.

Use the square at the bottom-right of the selection boundary, and drag the selection downwards for a large number of rows (again up to row 50 should be sufficient).

TODO

Placeholder data

The indicator now contains a number of queries and formulas, but it may not be immediately clear whether the formulas are set up as intended. To help see whether the calculations take place as expected, placeholder data can be entered in the Excel.

Select cell D10, and enter as content "Example".

Select cell E10, and enter as content "1750".

Select cell F10, and enter as content "2000".

TODO

These values are temporary stand-ins for what kind of data might be injected by the Tygron Platform upon (re)calculation.

See the results of the fomulas in cells L10, and V10 and X10. Notice how now that there is data to calculate, intermediate and final results are calculated and displayed.

TODO

Deploy the new Indicator

Finally, use the save-as option of the application to save the resulting file to a location on the computer where you can find it again later. Name the file "Built area overview".

TODO

In the Editor, add a new Indicator:

Editor → Current Situation (Ribbon tab) → Indicators (Ribbon bar) → Empty Indicator (Dropdown)

TODO

Set the new Indicator's name to "Built area".

Set the new Indicator's short name to "Built".

TODO

Click on "Select Excelsheet".

TODO

Click on "Add local file".

TODO

Select the "built area overview" Excel that was just created, and confirm the selection so that it is uploaded and added to the overview of uploaded Excels.

TODO

Select the newly uploaded file so that the entry is highlighted, and click on "Select" to select this Excel file for the Indicator.

TODO

Recalculate the Project if necessary.

TODO

Click on the new "Built" Indicator in the top bar, to open the indicator panel.

TODO

The contents of the Indicator will be a list of all Neighborhoods in the Project, as well as how much of the Neighborhood is built in. (Landsize, divided by lotsize.)

TODO

Note that, for now, the "score" in the Excel template has not been configured. There is a "SCORE" cell, otherwise using the Excel for an Indicator would result in an error. However, the cell is left with the default of 1, meaning the Indicator will always display 100%.

Optional Assignment

This assignment is intended to demonstrate the importance of working with "clean" indicator files

1. Download the debug excel of the Built Area Indicator, and open it in Microsoft Excel.
2. Take note of the amount of entries in columns D, E, and F.
3. In column D, replace the bottom 2 Neighborhood names with the letters "A", and "B".
4. Create 2 additional rows of data directly below, with Neighborhood names "C" and "D".
5. Save the file under a distinct name, and create a new Indicator with that saved Excel.
6. Recalculate, open the Indicator Panel, and take note of the Neighborhood name entries displayed.

What you see is the result of the Excel file containing data being uploaded into a Project which does not have enough Neighborhoods to overwrite all data. Remaining rows are untouched, meaning the placeholder data persists. For this reason, filled and/or debug Excel files should not be used as Indicator files. If an Indicator Excel ends up in a Project with insufficient data to overwrite the placeholder/debug data, the placeholder data will end up in the output and possibly even affect calculations.

Creating overviews of grid results

With a simple example Indicator now in place, more complex calculations can be created and included in the Excel, to provide more insight into how the various Neighborhoods are doing. One especially useful function is the ability to aggregate spatially calculated results, as from Grid Overlays, per subdivision in the Project. A concrete example is the calculation of traffic noise. The result is a detailed grid with exact calculated values for every location in the Project. To help interpret a near-analog spatial result, aggregating the results (like by averaging them) per Neighborhood creates a simpler overview of the results and allows for easier comparisons between different locations.

For this part of the tutorial, a spatial calculation of Traffic Noise will be referenced, and an overview will be created of the amount of housing units which experience an excessive amount of noise from traffic.

Add a Traffic Noise Overlay.

Editor → Current Situation (Ribbon tab) → Overlays (Ribbon bar) → Environmental (Dropdown) → Traffic Noise

TODO

In preparation of referencing the Overlay from the Indicator, an additional step is strongly recommended. Using TQL syntax, a Grid Overlay can be referenced via a syntax of the following form:

GRID_IS_0

Similarly to how it may not be known beforehand how many Neighborhoods exist in any given Project, the exact internal ID of an Overlay may not be known either. To bridge this, a different syntax can be used instead which will reference an Overlay not by ID but by (unique) Attribute. Since Attributes can be defined by a user, this allows any Overlay to be "targeted" by an Indicator.

With the Traffic Noise Overlay selected, in the right panel, switch to the "Attributes" tab.

TODO

At the bottom of the right panel, find the "Add new Attribute" fields. Enter as a name "EXAMPLE_OVERLAY", and as value "1".

TODO

Click on "Save New Attribute" to save the Attribute to the Overlay.

TODO

The Traffic Noise Overlay can now be referenced using the following clause:

GRID_WITH_ATTRIBUTE_IS_EXAMPLE_OVERLAY

TODO

This means as much as "referencing the Grid Overlay which has the attribute EXAMPLE_OVERLAY".

Return to the "built area overview" Excel file, opened in Microsoft Excel.

TODO

Use save-as to save the Excel file under a new name "traffic noise disturbance.xlsx".

TODO

Queries to retrieve data

In the Excel file, a number of additional queries can now be added to retrieve more information on the Neighborhoods and data of the Traffic Noise Overlay.

Select cell G9. Click in the address field, and enter the following text:

SELECT_UNITS_WHERE_NEIGHBORHOOD_IS_X

Ensure the letters match exactly. Hit enter on the keyboard to confirm the name of the cell.

TODO

Select cell H9. Click in the address field, and enter the following text:

SELECT_GRIDAVG_WHERE_NEIGHBORHOOD_IS_X_AND_GRID_WITH_ATTRIBUTE_IS_EXAMPLE_OVERLAY

Ensure the letters match exactly. Hit enter on the keyboard to confirm the name of the cell.

TODO

Now, all columns for requesting data are in use, but one more is needed for an additional query.

Select column I. Right-click on it, and select "insert".

TODO

This inserts an additional column with the formatting of the preceding column.

TODO

Select cell I9. Click in the address field, and enter the following text:

SELECT_UNITS_WHERE_NEIGHBORHOOD_IS_X_AND_GRID_WITH_ATTRIBUTE_IS_EXAMPLE_OVERLAY_AND_MINGRIDVALUE_IS_60

Ensure the letters match exactly. Hit enter on the keyboard to confirm the name of the cell.

TODO

For legibility, enter the texts "Housing units", "Traffic Noise", and "Disturbed housing units" as values in cells G7, H7, and I7 respectively.

TODO

Calculations

Next, the new data can be used to perform calculations regarding traffic noise disturbances.

Select cell N10. This is the top cell in the second column of light-blue cells.

TODO

Enter the following formula:

=IF(G10=0;0;I10/G10)

TODO

For legibility, enter the text "Disturbed fraction" in cell N7.

TODO

Outputs

Now, the output of the Excel can be changed as well, so that the output provides an overview of traffic noise disturbances.

Select cell Y10 again, the first cell in the second green column.

TODO

Enter the following formula:

=IF(D10="";""; ROUND(F10/10000;0) & " ha" )

TODO

Select cell AA10.

Enter the following formula:

=IF(D10="";""; ROUND(H10;1) & " dB" )

TODO

Select cell AC10.

Enter the following formula:

=IF(D10="";""; ROUND(I10;0) & " / " & ROUND(G10;0) )

TODO

Now, one more column should be added to the output of the Excel, so that the amount of significantly affected housing units can be displayed as well.

Select columns AB and AC and right-click on them, and click on "copy"

TODO

Right-click on column AD, and click on "Insert copied cells".

TODO

2 new columns are now added to the output.

TODO

Note that in this operation, a column was copied from inside the table, and inserted into the table. This is important because the first and last columns of the table html have slightly different syntax to properly create the rows desired.

Select cell AE10.

TODO

Enter the following formula:

=IF(D10="";""; ROUND(N10*100;0) & " %" )

TODO

In cell Y7, change the text to "Size".

In cell AA7, change the text to "Average traffic noise".

In cell AC7, change the text to "Affected housing units".

In cell AE7, change the text to "Percentage".

TODO

Select the range of cells N10 through AF10. (This covers both the newly created calculation as well as the newly created outputs.)

Use the square at the bottom-right of the selection boundary, and drag the selection downwards for a large number of rows (again up to row 50 should be sufficient).

TODO

Placeholder data

Now that the calculations of the Excel have been changed, the type of placeholder data used in and through the calculation can be amended as well.

Select cell G10, and enter as content "300".

Select cell H10, and enter as content "58".

Select cell I10, and enter as content "19".

TODO

These values are again temporary stand-ins for what kind of data might be injected by the Tygron Platform upon (re)calculation.

See the results of the fomulas in cells N10, and Y10, AA10, AC10 and AE10. Notice how now that there is data to calculate, intermediate and final results are calculated and displayed.

TODO

Deploy the new Indicator

Finally, save the excel file.

TODO

In the Editor, add a new Indicator:

Editor → Current Situation (Ribbon tab) → Indicators (Ribbon bar) → Empty Indicator (Dropdown)

TODO

Set the new Indicator's name to "Traffic noise".

Set the new Indicator's short name to "Traffic noise".

TODO

Click on "Select Excelsheet".

TODO

Click on "Add local file".

TODO

Select the "traffic noise disturbance" Excel that was just created, and confirm the selection so that it is uploaded and added to the overview of uploaded Excels.

TODO

Select the newly uploaded file so that the entry is highlighted, and click on "Select" to select this Excel file for the Indicator.

TODO

Recalculate the Project if necessary.

TODO

Click on the new "Traffic noise" Indicator in the top bar, to open the indicator panel.

TODO

The contents of the Indicator will be a list of all Neighborhoods in the Project, as well as the average traffic noise and the amount of housing units experiencing more than 60 dB.

TODO

Score

An Indicator not only shows information on a theme, but can in most cases compute a net score as well.

Scores serve a dual purpose:

• They allow for extremely succinct summaries of how well a Project Area is doing on any given theme, providing both quick insight and a common comparator.
• They allow for a concrete (albeit undetailed) value to inspect and understand even when being inspected for someone less knowledge about the theme in question.

In the case of traffic noise, no houses should be affected by too much traffic noise. The better the ratio of houses which are not affected by traffic noise, the higher the score.

Return to the "traffic noise disturbance" Excel file, opened in Microsoft Excel.

Select cell O10. This is the top cell in the third column of light-blue cells.

TODO

Enter the following formula:

=IF(G10=0;0;G10)

TODO

For legibility, enter the text "Total housing units" in cell O7.

TODO

Select cell P10. This is the top cell in the third column of light-blue cells.

TODO

Enter the following formula:

=IF(I10=0;0;I10)

TODO

For legibility, enter the text "Affected housing units" in cell P7.

TODO

Select cells O10 and P10.

TODO

The selection boundary around the cell has a small square in the lower right corner that can be used to drag and expand the formula to other cells as well.

Click-and-hold the square, and drag it downwards for a large number of rows (up to row 50 should be sufficient).

TODO

These columns of values can be used to calculate a ratio of disturbed housing units in relation to all housing units, for the totallity of all Neighborhoods. That ratio is then the inverse of the score. The lower the ratio, the greater the score.

Select cell B2, which is the 'SCORE' output cell.

TODO

Begin by computing the ratio of disturbed housing units, aggregated across all Neighborhoods.

Enter the following formula:

=SUM(P10:P50)/SUM(O10:O50)

TODO

This computes the ratio of houses. However, the score is the inverse of the ratio.

Next modify the formula so that it reads as follows:

=1-SUM(P10:P50)/SUM(O10:O50)

TODO

This results in a normalized score. It's good practice to add to this a restriction to keep the score within a bound of 0 and 1. 0, so that scores don't become negative. 1, as any value higher will cause the Tygron Platform to return a warning. Modify the formula so that it reads as follows:

=MIN(1; MAX(0; 1-SUM(P10:P50)/SUM(O10:O50) ))

TODO

Save the Excel file.

TODO

In the editor, go to:

Editor → Current Situation (Ribbon tab) → Indicators (Ribbon bar) → Traffic Noise (Left panel)

TODO

In the right panel, find the "Excel" input area.

TODO

Click on "Select Excelsheet" to reopen the "Excel selection" window.

TODO

The Excel file currently used in the Indicator is highlighted.

In the highlighted entry row, click on the "update" icon.

TODO

This will reopen the file selection screen.

Select the local Excel file, and confirm to upload it to the Project.

TODO

Recalculate the Project if neccesary.

TODO

Hover over the Indicator in the top bar.

TODO

You can now see a score is calculated, based on the calculation's result.

Important note about X-queries

When working with X-queries, an Excel file is pre-loaded with the relevant individual queries it entails. In other words, when a query such as the following exists:

SELECT_NAME_WHERE_NEIGHBORHOOD_IS_X

Then in a Project with 3 Neighborhoods, it will resolve into

SELECT_NAME_WHERE_NEIGHBORHOOD_IS_0
SELECT_NAME_WHERE_NEIGHBORHOOD_IS_1
SELECT_NAME_WHERE_NEIGHBORHOOD_IS_2

And any recalculation will effectively only rerun those queries.

In most situations, this behavior is sufficient. However, if the list of Neighborhoods changes (i.e. new Neighborhoods are added, or existing ones are removed), then the list of pre-loaded queries must be explicitly redetermined.

Hover over the recalculation icon in the top left of the Editor.

TODO

Click on "Reset-X Queries".

TODO

This serves the same function as a "normal" recalculation, but includes explicitly redetermining the existance of X-queries and expanding it into relevant queries.

Optional assignment

This assignment is intended to demonstrate the point of attention for editing data used in X-queries.

1. In the Editor, add an additional Neighborhood. (You don't need to actually draw it geographically). Then, click on the recalculation icon in the top left of the Editor, to perform a normal recalculation. Observe the results in the Indicator.
2. Instead, now use the 'Reset X- Queries' recalculation option. Observe the results in the Indicator.

Advanced Techninque - Names by selection

With these basics set up, it is possible to explore a method for making and managing Excels for Indicators slightly more easily.

So far, named cells have been created by manually setting the names of specific cells. This is doable for small ranges of cells or for queries which are not susceptible to change. However, as more queries are added, or changed, or even generated through Excel formulas, it becomes easier to apply names of cells in different ways.

Return to the "traffic noise disturbance" Excel file, opened in Microsoft Excel.

Select cell D9, and copy its name.

TODO

Paste the name of that cell into the content of cell D8.

TODO

Do the same for cells E9 to E8, F9 to F8,G9 to G8, H9 to H8, and I9 to I8.

TODO

Open the name manager.

Select all the entries for query-named cells, and delete them.

TODO

Close the name manager.

Select any of the previously named cells. Notice it is now no longer named.

TODO

Now, select the entirety of rows 8 and 9.

TODO

Next to the option for the name manager, find the "create by selection" option. This allows the creation of names for cells based on the contents of adjacent cells.

TODO

A prompt will appear, indicating a direction. Ensure the option to create names based on the top cells is checked, and all other options are unchecked.

TODO

Click on "OK".

Now, select cell D9 again. Notice that it is now named, based on what the content of the cell right above it was.

TODO

Tutorial completed

Congratulations. You have now completed this tutorial. In it, you have learned how to create and manage excels for indicators in your project.