Best Practices for Global Parameters

1. Usage Scenarios

When performing data integration and analysis, parameters are needed to accomplish complex dynamic chart analysis. This best practice will help you use global parameters in Guan-Index dataset cards, SQL statements of direct database datasets, and SQL statements of view datasets, to better complete data analysis.

2. Overview

Global parameters refer to parameters that can be flexibly used in various stages of data integration and analysis in Guandata, such as in cards, dataset calculated fields, view datasets, and SQL of direct datasets. By using parameters, you can achieve complex dynamic chart analysis, such as switching analysis dimensions, switching analysis metrics, dynamic data classification, cross-table dynamic queries, etc.

3. Specific Cases

3.1 Global Parameters Support Different Types of Datasets

3.1.1 Date Parameter Filtering in Direct Database Datasets

Add date parameter-based filter conditions in the SQL statement of a direct database dataset, such as filtering data for a specific day, MTD, YTD, etc. The SQL should match the syntax of the connected database. For example, in MySQL:

• The following SQL filters data for the selected date. If the date parameter filter default value is set to the dynamic time macro {{{yesterday}}}, it will filter yesterday's data by default.

  

• Modify the WHERE condition in the SQL to filter MTD data, i.e., data from the 1st of the month to the selected date. If the date parameter filter default value is {{{yesterday}}}, it will filter MTD data for the month of yesterday by default.

• Modify the WHERE condition in the SQL to filter YTD data, i.e., data from January 1st of the year to the selected date. If the date parameter filter default value is {{{yesterday}}}, it will filter YTD data for the year of yesterday by default.

  

3.1.2 Date Parameter Filtering in View Datasets

Add date parameter-based filter conditions in the SQL statement of a view dataset to calculate member repurchase rate based on dynamic date selection. Member repurchase rate = number of members with purchase times (days) ≥2 / number of all members with consumption.

• Create a view dataset in the data center and add the following date parameter-based filter condition in the SQL statement.

• Create a card using the view dataset and add a calculated field

3.1.3 Date Parameter Filtering in Guan-Index Datasets

To filter Guan-Index datasets by date parameter, create a card using the Guan-Index dataset and add a field or date filter based on the date parameter. For example, display data for the financial month of the selected date. Suppose the financial month is from the 26th of the previous month to the 25th of the current month. If the selected date is 2021-06-15, the card displays data from 2021-05-26 to 2021-06-25; if the selected date is 2021-06-29, the card displays data from 2021-06-26 to 2021-07-25.

• Card field: Start date, field type: date. If the selected date <26th, display the 26th of the previous month; if the selected date ≥26th, display the 25th of the current month.

• Card field: Selected date, field type: text. Use this field in the card filter to filter the valid time period. Filter condition: selected date = 'Y'.

  

3.2 Global Parameters for New Field Filter Conditions

3.2.1 Total Value of Calculated Metrics

To calculate the proportion of sales of the selected store to the national total sales, use the following formula:

• Create a card calculated field: Store Judgment

• Sales of selected store

• Create a card calculated field: Sales Proportion

3.2.2 Cumulative Value of Calculated Metrics

For time dimension comparison of sales, select a date and compare the cumulative sales for the week, month, and year of that date.

• Create a card calculated field: WTD Sales

• Create a card calculated field: MTD Sales

 

• Create a card calculated field: YTD Sales

3.2.3 Display Dimensions with No Data in a Time Period

Some stores may have no data in a certain period, but you still want to display them in the chart and include them in the average calculation.

Usually, you can use ETL to fill in the data so that each store has a row for each day, and fill in 0 for sales with no actual sales. But if the dataset already contains all stores, you can use global parameters to write filter conditions to calculate sales, so that stores with no sales in the selected period are also displayed.

• Create a card calculated field for date filtering

• Create a card field for filtered sales. Add it to the value bar and set aggregation to sum.

• Create a calculated field for average sales per store. Use this field to set the card's reference line.

3.3 Flexible Time Filtering and Comparison

3.3.1 Query Inventory for Any Date

Use date parameters to query inventory for any date. Use Guandata's UDF to build a key-value query field, and use the date parameter in the UDF to query inventory for each store and product on any date.

• Use Guandata's UDF (date_range_build) in ETL to build a key-value inventory query field. In SQL, sort data by date, then use the UDF to build the inventory query field.

• Create a query field in the card based on the date parameter and add it to the value bar.

• Note:

Use date_range_lookup to look up inventory upwards; use date_range_get to look up inventory for the selected date.

3.3.2 Dynamically Filter Data for the Last 12 Months

Use a date parameter filter to control the card to display data for the last 12 months from the selected date. For example, if the date is 2021-06-06, the bar chart displays data from 2020-07-01 to 2021-06-30, sorted by month.

• Card field: Selected date, field type: text. Use this field in the card filter to filter data up to the selected month.

• Card field: Month descending, field type: numeric. Use this field in the card filter to filter the last 12 months. Filter condition: month descending ≤12.

3.3.3 Flexible Comparison of Metric Values on Different Dates

In some business scenarios, such as bank deposit balance indicators, you need to compare the value on the query date with the value at the end of the previous month and calculate the difference. For example, if the query date is 2021-01-16, the previous month's end date is 2020-12-31, and you need to calculate the difference between these two days.

• Card field: Query date, field type: date

• Card field: Sales on the day, field type: numeric

• Card field: Sales at the end of last month, field type: numeric

• Card field: Difference, field type: numeric

• Card field: Date filter, field type: numeric. Add this field to the card filter bar, filter condition: date filter = 1. This filters out the query date and the previous month's end date.

3.3.4 Flexible Comparison of Two Time Periods

With global parameters, you can freely filter and compare data for two time periods. For example, filter and aggregate sales for a free time period, and compare with another period's sales.

• Card field: Time period, field type: text, shows 'current period' or 'comparison period'. Use this field in the card filter, filter condition: time period not null.

3.3.5 Flexible Comparison of Sales Rankings for This Week and Last Week

To compare and track sales and sales rankings of products (regions/stores/brands, etc.), you can flexibly compare this week's and last week's sales rankings.

• Card field: Query date, field type: date

• Card field: Sales this week, field type: numeric. Use this field for advanced ranking calculation to get this week's ranking.

• Card field: Sales last week, field type: numeric. Use this field for advanced ranking calculation to get last week's ranking.

• Card field: Ranking change. Add this week's sales field to the value bar and set conditional formatting based on the comparison of this week's and last week's rankings.

Note: This week is defined as the 7 days before the query date up to the query date; last week is defined as the 14 days before the query date up to 8 days before. If your definition differs, adjust the formula accordingly. For example, if this week is the week of the query date and last week is the previous week, the formula is:

3.4 Flexible Switching of Metrics

3.4.1 Flexible Switching of Card Metrics

Use a text parameter filter to freely select numeric fields (metrics) and dynamically display the selected metric in the card.

• Card field: Metric, field type: numeric. Add this field to the value bar.

• Card field: Metric name, field type: text. Add this field to the summary metric bar above the card, aggregation: max.

3.4.2 Flexible One-to-One Comparison of Metrics

Use a text parameter filter to freely compare multiple metrics, or calculate the difference between any two metrics. For example, compare sales and forecast sales, or sales and YoY sales, etc.

• Card field: Metric, field type: numeric. Add this field to the value bar.

• Card field: Comparison metric, field type: numeric. Add this field to the value bar.

• Card field: Difference, field type: numeric. Add this field to the value bar.

• Card field: Comparison metric name, field type: text. Add this field to the summary metric bar above the card, aggregation: max.

3.4.3 Compare One Metric with Multiple Metrics

Use a text parameter filter to compare one metric with multiple metrics. For example, the chart only shows sales by default, but you can select one or more metrics for comparison.

• Create multiple card fields as comparison metrics, all set as numeric type.

• Add the fields to the value bar or stacked chart value bar, and set the corresponding advanced calculations.

• Since all metrics are added to the value bar, the legend in this case cannot be hidden. The system's built-in legend selection is different from this method, which defaults to showing only one metric, not all.

3.5 Flexible Switching of Analysis Dimensions

3.5.1 Flexible Switching of Card Display Dimensions

Use a text parameter filter to freely select the analysis dimension of the card. For example, you can choose to aggregate by category or by member type in the same card.

Card field: Metric, field type: text. Add this field to the dimension bar of the card.

3.5.2 Flexible Switching of Card Time Dimensions

Use a text parameter filter to dynamically display the card in different time dimensions, and use a quick date range filter to select different periods.

For example, you can choose to aggregate by year, month, week, or day in the same card. When the date dimension is 'year', select the last 3 years; when the date dimension is 'month', select from this year to today.

• Create year, month, week, day, etc. date dimension fields, all as text type.

• Based on text functions, create a date dimension field, set as text type, and add to the card dimension bar.

3.6 Special Effects for Filters

3.6.1 Tree Structure Dimension Filter

Use a text parameter to create a tree structure parameter filter for dynamic dimension switching. For example, when selecting all categories in the tree parameter filter, the table shows all product categories; when selecting a major category, the table shows subcategories; when selecting a subcategory, the table shows minor categories.

• Card field: Subcategory parameter judgment, field type: boolean. Use this field to create the "dimension" card field.

• Card field: Minor category parameter judgment, field type: boolean. Use this field to create the "dimension" card field.

• Card field: Dimension, field type: text. Add this field to the card dimension bar and use it in the card filter, filter condition: dimension not equal to "invalid".

• Page filter: Category dimension. Use the text parameter "Category Dimension" to create a parameter filter and manually add options, set as a tree structure.

3.6.2 Link Multiple Dimensions with a Single Filter

Add global parameters to display options for both dimension one and dimension two in a single filter. For example, dimension one (member type: regular, VIP) and dimension two (member type 2: online, offline) are many-to-many. The filter "member type" can show four options for both member classifications.

• Card field: Member type integration, integrates data from both dimensions, field type: text.

• Card field: Member type filter, field type: numeric. Add this field to the card filter. Filter condition: member type filter >0.

• Parameter filter: Use the text parameter "CustomerType" to create a filter on the page and manually set options.

3.6.3 Fuzzy Query SKU

In e-commerce, product titles often include various high-frequency search terms for optimization, making them unpredictable. This case uses specific flavor keywords in product names to filter products (i.e., fuzzy matching), and can filter multiple flavors at once (i.e., "or" relationship between flavors).

• Card field: SKU, field type: text. Use this field to create the "SKU filter" card field.

• Card field: SKU filter, field type: boolean. Add this field to the card filter, filter condition: True.

• When creating the 'SKU filter' card field, since the parameter filter input is multi-value, use Spark functions for array processing. See the Spark website for function usage.

3.6.4 Dynamically Highlight Selected Colors

By using text parameters, you can dynamically display the color of the selected item. The selected item will show a different color than others. For example, when East China is selected, it shows orange, and other regions show blue.

• Create card field "Region", field type: boolean.

• Add the "Region" field to the card color bar and set the color.

• Create a selection filter > set the linkage field to "Division" > default value: follow user attribute > set linkage: associate parameter "Region". In some scenarios, regional managers need to compare their region with others. By setting the filter default value to follow user attribute, the region's data is highlighted by default.

3.7 What-if Analysis

3.7.1 Sales What-if Analysis

Perform what-if analysis on metrics. Enter any uplift rate, and the table will automatically calculate the uplifted value. For example, calculate the increased sales based on the entered sales uplift rate.

• Card field: Uplift rate, field type: numeric.

• Card field: Uplifted sales, field type: numeric.

3.7.2 Predictive DuPont Analysis

Combine what-if analysis with DuPont analysis charts to decompose metrics and predict changes by entering multiple variable values.

• First-level card metric: overall member sales

• Second-level card metrics: VIP member sales, regular member sales

• Third-level card metrics: VIP member count, VIP member average purchase, regular member count, regular member average purchase

Fourth-level card metrics: number of members with consumption, VIP member ratio, regular member ratio

3.8 Dynamic Reference Line Changes

Global parameters can be used for dynamic reference lines. The card displays data for a date range, and the reference line changes according to the date range. For example, if the filter range is 10 days, the reference line value is 10. Idea: first get the filter range, then calculate the reference line value.

3.8.1 Calculate Dynamic Date Difference

The admin creates two date global parameters, start date {{{today - 5 days}}} and end date {{{today}}}. The card uses these two global parameters, creates a date difference field: datediff([DYNAMIC_PARAMS.End Date],[DYNAMIC_PARAMS.Start Date]), and the reference line also uses the date difference field.

Reference lines should use other fields, not fixed values. Select the newly created date difference field, aggregation: max.

3.8.2 Dynamic Date Display in Card

Create a new date field: if([New Date]>= [DYNAMIC_PARAMS.Start Date] and [New Date]<= [DYNAMIC_PARAMS.End Date], [New Date], null), only display data within the two date parameters, and put the new date field in the dimension bar. The filter bar only shows values with dates, excluding null. Now the card setup is complete.

3.8.3 Filter Linkage for Dynamic Change

Global parameters in cards use default values. To achieve dynamic parameter passing, link filters and cards. Create two parameter filters, start date and end date. Do not use a date range or selection filter. The effect is that the card displays values for the period from start date to end date, and the reference line shows the date difference.

4. Operation Guide

1. In "Admin Settings > Resource Settings > Global Parameters", set the global parameters needed for the current enterprise domain (requires system admin permissions);
2. Use parameters to create calculated fields in Guan-Index dataset cards, or use parameters in SQL of direct/view datasets and create cards;
3. Create parameter filters on the page. Parameter filters will automatically link to the cards in step 2.

• Click the Guandata Help Center to view the steps for using global parameters.
• Visit the Atlas Cloud App Market to experience the view dataset and other product feature case libraries.