PPaT Wiki
Ngā tuhinga whakamāramaPathwayHuinga kōreroPae rangitakiWhakapā mai
Week streakFOUNDATION0 badges
Reo: Te reo Māori
  • English
  • Nederlands
  • Français
  • Deutsch
  • Lea faka-Tonga
  • Te reo Māori
  • မြန်မာ (Burmese)
Pātai atu ki te hapori

Your PaT Pathway

0/46
Foundation
0/43
Practitioner
0/37
Advanced
0/20
Super User
  • Hanganga Kamupene: ngā kōwae mō tētahi pae, whenua rānei
  • Hanganga Kamupene: te tāpiri kaiwhakamahi hou
  • Hanganga o te Kamupene: ngā tari, ngā taitara tūranga me ngā tūranga mahi
  • Me pēhea te waihanga kaupapa mō te tomokanga whakangungu PaT Lite
  • Me pēhea te whakarite i tō Mahere SD
  • Me pēhea te whakatika i tētahi whakairinga kei te Papa Pānui
  • Ngā Hononga: te hono i PaT ki Microsoft 365
  • Ngā Ine LMS – Ki te kore e taea e koe te ine, kāore e taea e koe te whakahaere
  • Ngā Puka - Te Whakaurunga API
  • Ngā Urupare LMS: ngā whakatauranga me ngā kōrero a ngā ākonga
  • Ngohe Pūnaha: ngā pikinga take, ngā takiuru, ngā īmēra me ētahi atu
  • PaT He Tirohanga Whānui ki te Tātauira Wāhanga Pakihi
  • SD Ngā Rerengamahi - Ngā Tautuhinga Rerengamahi
  • SD Ngā Rerengamahi - Tuku Maha
  • Te āta whakarite i ngā arotake anō me ngā whakamōhiotanga piki taumata
  • Te Hanganga o te Kamupene: ngā kamupene kiritaki
  • Te Hanganga o te Kamupene: ngā kāwai tukanga
  • Te tūtohi hanganga whakahaere
  • Whakahaere kaiwhakamahi
  • Whirihoranga Pūnaha: ngā tautuhinga kamupene
  • Aroturuki i te whai wāhi
  • Īmēra ki BFF
  • Kōrerorero ki SD
  • Mai i te īmēra ki te kōrerorero, ā, hoki ki te īmēra – mō ngā tāngata kāore e whakamahi ana i PaT
  • Mai i te kōrerorero ki te īmēra, ā, hoki anō ki te kōrerorero
  • Mai i te kōrerorero ki te mahi, ki te kaupapa
  • Me pēhea PaT e whakahaere ai i ngā kaiwhakamahi hohe-kore
  • Me pēhea te wetewete i tētahi kōwae ako mō tētahi kaimahi
  • Ngā Āhuatanga Kōrerorero Matapihi-ake
  • Ngā Āpitihanga C2E
  • Ngā Arotake Māramatanga
  • Ngā arotake taunaki: te whakaoti i tētahi
  • Ngā Ata Tohu Mōhiohio
  • Ngā Hīkoi Haumaru
  • Ngā Kōrerorero Kua Tāmaua
  • Ngā kōrerorero rōraina
  • Ngā Mahi a te Kaipupuri SD
  • Ngā mahi auau: te waihanga me te whakatika
  • Ngā rūma ataata: te hono mai a ngā manuhiri me te tono kia tuhi
  • Ngā tauira hauora me te haumaru
  • Ngā Tūmahi Maha
  • Ngā whakamahinga rerekē o ngā puka
  • Nuku i ngā Pane Wāhanga
  • PaT AI
  • Pātū Puka
  • SD Ngā Whakautu Puka: te kimi me te kaweake i ngā whakautu puka
  • Tāpirihia he Atahanga hei Ata o te Pane Wāhanga
  • Te āhua o ngā kōrerorero a SD me ngā Mema o SD me ngā Kaiwhakamahi o Waho
  • Te Hanganga o te LMS
  • Te Pono ki ngā Mahi: he aha tēnei, ā, he aha i heke ai
  • Te tāpiri raraunga ora ki tētahi SD (ngā kauwhata me ngā ripakaute)
  • Te tautapa i tētahi SD ki tētahi mātua hou
  • Te whakahaere hui mā ngā SDs
  • Te whakamahi i ngā SDs hei taupānga pūrongo, hei taupānga hoki mō ngā meneti hui
  • Te whakarōpū me te tiki anō i ngā SDs
  • Te Whakatūunga i ngā SDs me te Whakahōu i ngā Atahanga
  • Te whakauru i ngā SDs ki roto i ngā SDs
  • Tiritiria, tuhia hoki tō mata
  • Tirohanga pakitara Tūmahi SD
  • Tonoa ngā Tāngata kia Hono Mai
  • Whakamahara kōrerorero
  • Whakangungu motuhake mā te kiritaki: te āhua o te whakahaere
  • Whakarōpūtanga Huānga o SD
  • Arokite, tikiake, ka tohaina ngā kōnae
  • He raruraru ki te manatoko-ā-taumata-rua o Outlook
  • Hopukina, Whakahaerehia hoki ngā aituā
  • Hurihia te kōrerorero hei tūmahi
  • Hurihia tētahi Tuhipoka hei Tūmahi
  • Kei Waho i te Tari: te whakakā, me te maramataka kei tō Pātū Tūmahi
  • Kōkuhutia ngā hononga
  • Kupu Whakataki mō te PaT mā te Tauhou – Ngā Kōrerorero me ngā SDs
  • Me pēhea te whakamahi i te tomokanga whakangungu PaT Lite
  • Me pēhea te whakarerekē i te rahi o tō pūtirotiro kia pai ai te tirohanga ki PaT
  • Me pēhea te whakauru i a Outlook ki tō PaT
  • Ngā āhuatanga whaihua o te whārangi matua o SD
  • Ngā Ākonga o te Kōwae – Waea Pūkoro
  • Ngā Kōrerorero ā-Rōpū
  • Ngā Kōwae Ako - Papamahi
  • Ngā Pane Wāhanga
  • Ngā Pānui Mahi
  • Ngā pānui mō ngā whakahou: he kōrerorero nā Sys Bot ina rerekē ngā pitopito tuku a PaT
  • Ngā tikanga haumaru mō ngā kaiwhakamahi maha i runga i te pūrere
  • Ngā Toronga Chrome Hirahira
  • Ngā tuhipoka
  • Ngā Tuhipoka Tere
  • Ngā waea ataata i te taupānga pūkoro
  • PaT He tirohanga whānui ki te Kōrerorero
  • PaT te āhua o te ratonga: te whārangi āhua, ngā matohi āhua me ngā pānui āhua
  • PaT Wiki: te rapu ara
  • PaT Wiki: te rapu, te whakautu a te AI me te tuku whakatauranga
  • Pātaka Tuhinga: ngā hononga tiritahi, ō kōpaki me ngā mea tino pai ki a koe
  • Pātū Tūmahi
  • Pūnaha Whakahaere Tūmahi
  • Rapu Whānui
  • SD Ngā Huānga
  • SD Tirohanga whānui
  • Taku Kōtaha
  • Tāpiri mema me te whakarite whakaaetanga
  • Taupānga Pūkoro PaT – Tirohanga Whānui
  • Te ahunga whakamua ā-rama waka
  • Te ata hono i runga i tō papamahi PaT
  • Te Hopu me te Whakahaere i ngā Mōrearea
  • Te pānui i tētahi SD ki tētahi atu reo
  • Te Pātū Kōrerorero
  • Te takiuru ki PaT me te tautuhi anō i tō kupuhipa
  • Te takiuru mā te MFA: te taahiraa tuarua
  • Te tiki āwhina: te Papamahi Āwhina PaT
  • Te Whakahaere Tūmahi
  • Te Whakawhiti Kōrero ā-Pūtaketanga
  • Tiri Urupare
  • Tukuatu ngā āpitihanga kōnae
  • Waihangaia he Tūmahi
  • Waihangatia he SD hou
  • Whakaputa i tētahi Tuku ki te Papa Pānui
  • Whakarāpopototanga AI: he whakarāpopototanga tere o tētahi SD
  • Wiki a te kamupene: te rapu i ngā kōwae ake a tō kamupene
  • Aratau Whakamātau Puka
  • Calculated fields and calculated columns
  • Charts: options for lines, bars, axes and single values
  • Data sources: stage filters, dynamic dates and page values
  • Form Builder: layout at each screen size
  • Form Builder: tools and shortcuts
  • Grids linked to a reporting SD: history charts and review comments
  • Grids: columns, formats and filters
  • Grids: conditional formatting
  • Grids: flatten and transpose data
  • Grids: rows, default values and editing rules
  • Hangaia he pūnaha pūrongo
  • Kaihanga puka: ngā momo āpure
  • Kaiwhakahaere Kōwae: te kimi me te whakahaere kōwae
  • Kaiwhakahaere Playbook: kua rite tō oma ātārangi mō te arotake
  • Kaiwhakahaere Playbook: ngā tīkiti kei muri i ia kāwai Kounga me te Utu
  • Kaiwhakahaere Tohu Whakamutunga: te kimi tohu whakamutunga me te hanga i tāu ake UDF
  • Kaiwhakatika Playbook: te tāpiri i tētahi hipanga rapu i te pae tomokanga
  • Kaiwhakatika pukapuka mahi: te tāpiri i tētahi hipanga hei whakakore i tētahi ota
  • Me pēhea te hanga arotake taunakitanga mō ngā kōwae ako
  • Me pēhea te tāpiri i tētahi SD ki te LMS
  • Me pēhea te tuku i ngā huānga puka ki tētahi SD
  • Me pēhea te waihanga aromatawai mārama mō ngā kōwae ako
  • Me pēhea te waihanga kōwae i te Kaiwhakaputa Kōwae
  • Me pēhea te waihanga patapātai kōwae
  • Me pēhea te whakahohe i ngā tauira puka kia whakamahia e te katoa
  • Me pēhea te whakakā a ngā kaiwhakahaere i te OOF mō ā rātou kaimahi
  • Me pēhea te whakamahi i te PAR SD
  • Ngā āpure e hiahiatia ana, te whakamana me ngā ipu
  • Ngā āpure puka: ngā uara taunoa, te aratau pānui-anake me ētahi atu tautuhinga
  • Ngā Āwhina Tātauira SD
  • Ngā Kōwhiringa Ihirangi mō te Kaiwhakaputa Kōwae
  • Ngā pātene puka, ngā PDF me ngā mea ka tupu i muri i te tukunga
  • Ngā Puka – Tango Raraunga (te kaihanga puka AI)
  • Ngā Puka SD
  • Ngā Puka Tōtika
  • Ngā Tautuhinga Haumarutanga SD
  • PaT SD He tirohanga whānui ki ngā tauira
  • PaT SD Tirohanga whānui ki te Mahere
  • PaT SD Whakaaturanga Mahere
  • Pūnaha Rautaki
  • Pūrongo Hauora me te Haumaru (H&S) Wā Tūturu
  • Rich text, file uploads and speech to text
  • SD Papatohu: te hanga papatohu
  • Stacked views, flip cards, tabs and stages
  • Te Aratohu AI: te tirotiro i ētahi atu tīkiti he ōrite te tau
  • Te Whakahaere Kōrerorero – He Tohutohu Mātauranga
  • Te Whakahaere Matatau i te Tātauira Rautaki Pakihi
  • Te whakamahi i ngā SDs hei Tuhinga LMS
  • Te whakamahi i te Chat to SD kia neke atu i te kaiwhakahaere pai ki te kaiwhakahaere tino pai
  • Te Whakarite Whakamōhiotanga Whakaoti Kōwae
  • Te whakawhāiti i te tere
  • Te whirihora i tētahi UDF: ngā tātari, te whakarōpū me ngā taipitopito pito-mutunga
  • Te whirihora i tētahi UDF: ngā tīwae me ngā tīwae tātai
  • Whakahōputu Herekore
  • Whakakake aunoa
  • Whakangungu mō ia kiritaki ake: te whakarite
  • PaT Lite: Ngā āhuatanga matua o PaT me mārama koe
  • He aha a PaT, ā, he aha te take nui
  • PaT Lite: Kōrerorero hei Tūmahi
  • PaT Lite: He kupu whakataki ki ngā Strategy Documents
  • PaT Lite: Te Whakaaro Matua – Ngā Paerewa me te Whakatinanatanga Rautaki
  • PaT Lite: He taputapu atamai e whakaaro tahi ana me koe
  • PaT Lite: Ko te Ahurea te Anamata o te Pakihi
  • Haumarutanga
  • Ngā whakaurunga
0/46
Foundation
0/43
Practitioner
0/37
Advanced
0/20
Super User
  1. Tuhinga āwhina
  2. Arā Atu Anō
  3. Calculated fields and calculated columns

Kāore anō tēnei whārangi kia whakamāoritia, nō reira kei te whakaaturia ki te reo Pākehā.

Calculated fields and calculated columns

Summary A Calculated Field works out a number from other answers on a form, such as a total cost from a quantity and a unit price, while the form is filled in. A calculated column does the same in a…

Check yourself

E tirohia ana tō takiuru PaT…

How To

Kei tēnei whārangi
  • Adding a calculated field to a form
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5
  • Building the formula in the Calculation Builder
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5
  • Step 6
  • Step 7
  • Step 8
  • Step 9
  • Step 10
  • Step 11
  • What a formula can use
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5
  • Step 6
  • Step 7
  • Step 8
  • Step 9
  • What people filling in the form see
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5
  • Step 6
  • Step 7
  • Step 8
  • Calculated columns in a UDF
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Calculated columns on a grouped report
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5
  • Step 6
  • Step 7
  • Step 8
  • Step 9
  • Step 10
  • Step 11
  • Step 12
  • Step 13
  • Choosing a calculated column's decimal places
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5
  • Step 6
  • Step 7
  • Purpose / Intent of the Module
  • Header One

Summary A Calculated Field works out a number from other answers on a form, such as a total cost from a quantity and a unit price, while the form is filled in. A calculated column does the same in a UDF, the data behind your grids and dashboards: on a grouped report it works from the figures the grid shows for each group, as a spreadsheet would, and you choose how many decimal places it is rounded to. This lesson is for the people who build forms in the SD Form Builder and set up UDFs in the SD Endpoint Manager.

Adding a calculated field to a form

Step 1

A calculated field works out a number from other answers on the same form, such as a total cost from a quantity and a unit price. The person who builds the form adds it in the SD Form Builder: open the SD, click Advanced, then click SD Form Builder in the menu on the left.

Step 2

First add the fields the formula will use, such as two Text (Single line) fields called Quantity and Unit Price. Give each field its own Title.

Step 3

On the left of the form builder, open the drop-down list under Controls and choose Common.

Step 4

Calculated Field is the last element in the Common list. If you cannot see it, click Next under the list.

Step 5

Drag Calculated Field onto the Canvas. Until it has a formula, it reads Expression: No expression set. Its result box stays empty in the form builder: the result is only worked out when the form is filled in.

Building the formula in the Calculation Builder

Step 1

Click the calculated field's edit button, the pencil at its top right. The Edit form field Calculated Field window opens.

Step 2

On the General tab, type the name people will see in Title, such as Total cost.

Step 3

Click the abacus button at the right end of the Expression Value box: pointing at it shows Open Calculation Builder. The Calculation Builder opens.

Step 4

Under Available Fields, click the first field the formula uses, such as Quantity. PaT adds it to the Expression box by its reference, between curly brackets.

Step 5

To find a field, type part of its name in Search fields. Each field in the list shows its title and its reference, and a field inside a Field Group also shows the group's title.

Step 6

Under Operators, click the operator you need, such as *. The buttons are + to add, - to subtract, * to multiply and / to divide. The ( and ) buttons put part of the formula in brackets, so it is worked out first.

Step 7

Click the next field, such as Unit Price. The Expression box now holds both references with * between them, and Preview, below the list, shows the formula as it stands.

Step 8

You can also type in the Expression box, for example a number such as 100. Clear, beside the operators, empties the box, and Cancel closes the builder without keeping your changes.

Step 9

Click Save Expression, at the bottom right of the Calculation Builder. The builder closes, and the formula appears in Expression Value.

Step 10

Click Save and Close Calculated Field, at the bottom right of the window. On the canvas, Expression: now shows your formula.

Step 11

If the Canvas heading says (Unsaved changes), click Save Form Now at the top of the canvas to keep the new field.

What a formula can use

Step 1

Available Fields lists only the fields a formula can use: Text (Single line), Dropdown (Single selection), Slide Bar, Star Rating and other calculated fields.

Step 2

A formula can use another calculated field, so one result can build on another, such as a subtotal plus freight. Available Fields leaves out the calculated field you are editing, so it cannot be added to its own formula.

Step 3

Use only field references, numbers, the four operators and brackets. Anything else, such as a word or a percent sign, makes the result read Invalid expression.

Step 4

For a percentage, divide and then multiply by 100: put the division in brackets, click *, then type 100.

Step 5

In the fields a formula uses, people should type plain numbers, such as 1200 or 12.5. PaT also reads 1,200 as 1200 and $12 as 12. An answer such as abc or 12% does not count as 0: the result shows a message instead.

Step 6

A Dropdown (Single selection) counts the option chosen as a number, so give its options labels that are numbers, such as 1, 2.5 and 3. An option that is not a number counts as 0, and an option such as 2.5 counts as 2.5, also on older forms.

Step 7

Slide Bar and Star Rating count the number chosen.

Step 8

A field's reference is made from its Title, in lower case with hyphens for spaces. Under Available Fields, each field shows its Reference. If you change the Title of a field a formula uses, open the Calculation Builder and check that the formula still uses that field's reference: PaT counts a reference it cannot find as 0.

Step 9

Give each field a formula uses its own Title. When the same Title is in two Field Groups, such as after you duplicate a group, each group's calculated field uses the field in its own group.

What people filling in the form see

Step 1

The person filling in the form cannot type in a calculated field: PaT fills it in from their other answers. A calculated field is always read only, so its window has no Read only switch.

Step 2

The result updates about half a second after they stop typing or choosing in a field the formula uses.

Step 3

The result always shows 2 decimal places, such as 12.50. A calculated field on a form has no setting to change this.

Step 4

An empty field counts as 0, so the result can read 0.00 before anything is filled in.

Step 5

If the formula divides by a field that is empty or 0, the result reads Invalid calculation until that field has a number. If an answer the formula uses is not a plain number, the result shows a message instead, such as "abc" is not a plain number.

Step 6

The result is saved with the other answers. Form Response Column Header Text, on the Advanced tab of the field's edit window, sets its column heading in the form responses.

Step 7

To test the formula before you share the form, click Preview at the top left of the form builder. In Preview (Emulated Size), fill in the fields the formula uses and check the result.

Step 8

To show the calculated field only when an earlier answer calls for it, use its Conditional Logic tab, as the lesson Conditional Formatting shows.

Calculated columns in a UDF

Step 1

A calculated column does the same job in a UDF, the data behind your grids and dashboards. The person who sets up the UDF adds it in the SD Endpoint Manager, on an SD's Advanced tab under PaT BI.

Step 2

Find your UDF and click Edit Endpoint on its card. Configure Data Source opens.

Step 3

Click the Columns tab, then click Add New Calculation Column. When a column is already selected, click Add Column at the top of its settings instead.

Step 4

The lesson Configuring a UDF: columns and calculated columns shows how to name the column and build its formula with Edit Expression. The steps below add grouped reports and decimal places.

Calculated columns on a grouped report

Step 1

A grouped report shows one row per group, such as one row per region. To group a UDF, click the Query Builder tab, expand GROUP BY, switch on Enable GROUP BY and choose the column in Group by (Level 1).

Step 2

You can add a second level in Then group by (Level 2). The lesson Configuring a UDF: filters, grouping and endpoint details covers grouping.

Step 3

On the Columns tab, click each number column the report totals. Under Aggregation Type, set Aggregation Method to SUM - Total of all values, so each row shows that group's total.

Step 4

Every other column needs an Aggregation Method too, except the grouped column and calculated columns. Until it has one, its Aggregation Type says Aggregation required.

Step 5

In the preview grid, the grouped column's header is marked L1, and a totalled column's header SUM.

Step 6

A calculated column on a grouped report reads its formula the way a spreadsheet user means it: each column in the formula is the figure the grid shows for that group, not the separate records behind it. That is the group's total for a column on SUM, its average for AVG, its count for COUNT, and its lowest or highest value for MIN or MAX. Columns multiplied together are worked out for each record first, then added up.

Step 7

For example, group by Region and set Sales and Target to SUM. In the calculated column's Calculation Builder, click Sales, click /, click Target, click *, then type 100. Each region's row shows its total sales as a percentage of its total target.

Step 8

Divisions and percentages work as written, with no special functions such as SUM in the formula.

Step 9

A calculated column cannot be grouped and has no Aggregation Type of its own: its Group By setting says GROUP BY is disabled for calculated columns. PaT works it out from the figures the grid shows for each group.

Step 10

The Query Builder's Group by (Level 1) and Then group by (Level 2) lists leave out calculated columns, so you group by an ordinary column.

Step 11

If a column the formula uses is not typed as Number, Expression Builder warns you. Click that column and set its Data Type to Number for clean results: a value that is not a number comes through blank.

Step 12

Click Apply to Data to save your changes and see the grouped rows with the calculated column.

Step 13

If you switch Enable GROUP BY off again, every column stays in the grid, including the totalled columns and the calculated columns.

Choosing a calculated column's decimal places

Step 1

On the Columns tab, expand Column Order and Visibility and click the calculated column, or click its header in the preview.

Step 2

Scroll down to Decimal Places, below Group By. It shows for calculated columns and for Number columns.

Step 3

Type how many decimal places to show, from 0 to 10. A new column starts at 2, and a number above 10 is set to 10.

Step 4

For example, in the preview a region at 87.46 percent of target shows 87.5 with 1 decimal place, and 87 with 0.

Step 5

The setting applies when the formula's result is a number. A text result, such as one made with SPLIT, shows as it is.

Step 6

Click Apply to Data to save the setting. The preview in the window shows the column with that many decimal places.

Step 7

Grids in forms and dashboards built on the UDF show the column with that many decimal places too, so with 0, 4 shows as 4, not 4.00.

Purpose / Intent of the Module

A brief description of what this module is about

Header One

Ready? Back to the quiz

Rate this page

Checking your PaT sign-in…

O muaAratau Whakamātau PukaO muri Charts: options for lines, bars, axes and single values

Kei tēnei whārangi

  • Adding a calculated field to a form
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5
  • Building the formula in the Calculation Builder
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5
  • Step 6
  • Step 7
  • Step 8
  • Step 9
  • Step 10
  • Step 11
  • What a formula can use
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5
  • Step 6
  • Step 7
  • Step 8
  • Step 9
  • What people filling in the form see
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5
  • Step 6
  • Step 7
  • Step 8
  • Calculated columns in a UDF
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Calculated columns on a grouped report
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5
  • Step 6
  • Step 7
  • Step 8
  • Step 9
  • Step 10
  • Step 11
  • Step 12
  • Step 13
  • Choosing a calculated column's decimal places
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5
  • Step 6
  • Step 7
  • Purpose / Intent of the Module
  • Header One
PPaT Wiki

He tuhinga, he pātengi raraunga mātauranga, he tautoko ā-hapori hoki mō People and Tasks.

Tuhinga

  • Te tīmatanga
  • Ngā ariā matua
  • Ngā tuituitanga
  • Tohutoro API Ākuanei

Tautoko

  • Pātengi raraunga mātauranga
  • Ngā pitopito kōrero tuku
  • Huinga ā-hapori
  • Whakapā mai
  • Tūnga ratonga

Kamupene

  • PaT
  • Tuhinga rangitaki
  • Ngā Ture
  • Tūmataitinga

© People and Tasks. Kua rāhuitia ngā motika katoa.