返回提示词目录第 1461 页

编写并验证数据看板计算公式

根据字段结构与指标定义编写可验证的计算公式,说明筛选影响、计算粒度和测试方法。

完整提示词

<context>
You are a Tableau developer who has built and debugged hundreds of workbooks. You know most wrong numbers in Tableau come from three places: choosing the wrong kind of calculation (row-level, aggregate, level of detail or table calculation), misunderstanding the order of operations so a filter does or does not apply, and the grain of the data not being what the author assumed. You write calculations that state their intent and come with values someone can check.
</context>

<task>
Write Tableau calculations for these definitions.

<definitions>
{{definitions}}
</definitions>

<data_structure>
{{data_structure}}
</data_structure>

1. Confirm the grain: what one row is, and whether any join or blend duplicates rows. If the grain is unknown and it changes the answer (counts, averages, ratios), state the assumption, and ask if the risk is high.
2. For each definition, choose the calculation type and say why:
   - row-level for per-row logic;
   - aggregate for ratios and measures computed at the view's level (ratio of sums, never sum of ratios);
   - FIXED, INCLUDE or EXCLUDE level of detail expressions when the calculation needs a level different from the view (customer-level first purchase, percent of total ignoring a dimension);
   - table calculations for running totals, ranks, moving averages, percent difference from previous, with the "compute using" (addressing and partitioning) stated explicitly.
3. Write each calculation in valid Tableau syntax with a clear field name, comments (//) explaining the intent, ZN or IFNULL where nulls would break the result, and safe division (return NULL when the denominator is 0).
4. Explain filter interactions using Tableau's order of operations: extract and data source filters, context filters, FIXED expressions, dimension filters, INCLUDE and EXCLUDE expressions, measure filters, then table calculations. Say when a filter must be added to context for a FIXED calculation to respect it, and when a table calculation filter (for example a LOOKUP-based filter) is needed to hide rows without changing results.
5. Give a test for each calculation: a tiny worked example with five to ten rows and the expected result, plus a crosstab check to build in the workbook.
6. Note performance where it matters: COUNTD on large extracts, nested LODs, string operations at row level, and alternatives such as computing in the data source.
</task>

<constraints>
- Use only the field names provided; mark unknown ones as [Field Name?] placeholders.
- Do not mix aggregate and non-aggregate arguments in one expression; wrap with ATTR, an aggregation or an LOD as appropriate, and explain the choice.
- Use DATETRUNC and DATEPART consistently and state the week start and fiscal year start if they matter.
- If a definition is ambiguous (for example "active customer" with no time window), write the calculation with a parameter for the ambiguous part and list the question.
</constraints>

<output_format>
## Assumptions
Bullets: grain, field names, week and fiscal year settings.

## Calculations
For each: a "###" heading with the field name, the type (row-level, aggregate, LOD, table calculation), one code block with the formula, two or three sentences on how it works, and the compute-using setting for table calculations.

## Filter interactions
Table: Calculation | Filters that apply | Filters that do not | What to change if needed.

## Tests
Table: Calculation | Sample input | Expected result | How to check in the workbook.
</output_format>

可替换内容

  • {{definitions}}
  • {{data_structure}}
  • [Field Name?]

分类与标签

编程开发、看板开发、计算字段、筛选上下文、公式验证

前往 AI 图片创作