Indicators Tutorial
- Name editing
- Name generating
- Debug sheet downloads
- Grid calculations
- Assignments
- Images
Prerequisites
The following prerequisites should be met before starting this tutorial:
- This tutorial is a continuation of the TQL tutorial. If you have not already done so, please complete that tutorial first. 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.
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.
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
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.
Open the Excel file in Microsoft 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.
Enlarge the address field by dragging the edge with the three dots to the right.
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.
In the formula field, the content of the cell can be entered. Enter the following text:
This is an example Indicator
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.
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.
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:
The left panel is now open with a list of all Indicators in the Project. The newly added Indicator is already listed.
The top bar has also appeared (if it was not displayed already), listing the newly added Indicator.
In the right panel, change the "name" of the Indicator to "Example indicator", and the "short name" to "Example".
In the right panel, find the "Excel" input area. Notice a default Excel file is already set, named "indicator.xlsx".
Click on "Select Excelsheet".
This opens the "Excel selection" window.
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".
Select the created Excel file, and confirm to upload it to the Project.
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.
Then, click on "Select" on the bottom right of the window to select it.
The "Excel selection" window will now close, and the created Excel file is now listed in the right panel.
Depending on the complexity of the Project, the Session may or may not automatically recalculate. 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".
When the calculation has completed, click on the Indicator in the top bar.
This opens the Indicator panel.
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.
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:
In the right panel, find the "Excel" input area.
Click on "Select Excelsheet" to reopen the "Excel selection" window.
The Excel file currently used in the Indicator is highlighted.
In the highlighted entry row, click on the "download" icon.
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.
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
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.
Create a query of the following form:
SELECT_LANDSIZE_WHERE_
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.
As value for this cell, enter the following value:
100
Return to the query tool, and create a query of the following form:
SELECT_LOTSIZE_WHERE_
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.
As value for this cell, enter the following value:
50
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 )
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 (" . ")
In the editor, go to:
In the right panel, find the "Excel" input area.
Click on "Select Excelsheet" to reopen the "Excel selection" window.
The Excel file currently used in the Indicator is highlighted.
In the highlighted entry row, click on the "update" icon.
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.
Recalculate the Project if neccesary.
Click on the Indicator in the top bar to open the indicator panel.
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.
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:
In the right panel, find the "Excel" input area.
Click on "Debug Excelsheet". This will open the file save window.
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.
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.
If insight into the debug Excel file leads to a change in the content of the Excel file. 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.
In Microsoft Excel, with the Excel file open, in the ribbon's header click on "Formulas", and then find the "Defined Names" section.
Click on "Name Manager". This will open the Name Manager.
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".
In the prompts that appears, change the name to the following:
SELECT_FLOORSIZE_WHERE_
Confirm the name change, close the Name Manager, and click on cell B5.
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.
Find an open spot in the 3D world where an additional building can be added.
Add a new Measure:
The new Measure is now added and selected in the left panel, and its details are visible in the right panel.
At the bottom of the left panel, click on "Add Building" to add a Building to the Measure.
The Building is added to the Measure, and its details are visible in the right panel.
Click on "Draw Area".
Draw a selection for the Building in the 3D Visualization, and click on "Apply Selection".
Select the Measure itself in the left panel, and in the right panel click on "Activate Measure".
This will start a test run, and add the Building to the 3D Visualization.
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.
On the left side of the ribbon, click on the "Stop" button of the test run to stop the test run.
This will restore the 3D Visualization to its original state.
Notice that the Indicator's results are also restored.
Assignments
- Upload the Excel with the changed cell name, and inspect the results.
- 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 neccesary, check whether your formula is going to divide by 0, and change your output accordingly.
- 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 can also be a little bit terse when not well-versed in it.
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://previewsupport.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.
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 managable 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 sensible subdivision.
This means that conceptually, a query such as the following:
SELECT_LANDSIZE_WHERE_
will 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".
In the Indicator template Excel file, opened in Microsoft Excel, select cell C9. This is one of the orange cells just above the dark-grey cells.
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.
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 Beighborhood on each row.
Select cell D9. 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.
Select cell E9. 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.
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 C7, D7, and E7 respectively.
Next, the desired calculations can be defined.
Select cell K10. This is the first cell in the rows and columns of light-blue cells.
Enter the following formula:
=IF(E10=0;0;D10/E10)
Hit enter on the keyboard to confirm the value of the cell.
This formula will see if there is a landsize defined. If there is (or mroe 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.
Select cell K10 again.
The selection boundáry 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).
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.
Finally, it is possible to configure the output for the Indicator.
Select cell U10. This is the first cell in the first green column, and is surrounded by pre-defined HTML for a table display. 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(C10="";"";C10)
This formula means that if C10 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 C10, (which should be the name of the Neighborhood, then that value is placed verbatim in this cell for output.
Next, select cell W10. 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.
Enter the following formula:
=IF(C10="";""; ROUND(K10*100;0) & " %" )
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 U7, change the text to "Neighborhood".
In cell W7, change the text to "Percentage built".
In cell Y7, change the text to " " (a single space).
In cell AA7, change the text to " " (a single space).
Select the range of cells T10 through AB10.
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).
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".
In the editor, add a new Indicator:
Set the new Indicator's name to "Built area".
Set the new Indicator's short name to "Built".
Click on "Select Excelsheet".
Click on "Add local file".
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.
Select the newly uploaded file so that the entry is highlighted, and click on "Select" to select this Excel file for the Indicator.
Recalculate the Project if necessary.