Skip to content

Commit aad5217

Browse files
thjenniferthjennifer
committed
fixed fiscal_periods_sdt group by statement, updated readme and release notes, reset marketplace constants, fixed typos
1 parent b6f689b commit aad5217

File tree

6 files changed

+47
-39
lines changed

6 files changed

+47
-39
lines changed

‎README.md‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -98,10 +98,12 @@ Each dashboard user can personalize these values by following these [instruction
9898

9999
- **Persistent Derived Tables**: If using this block with production data, you may want to convert some derived tables to [Persistent Derived Tables (PDTs)](https://cloud.google.com/looker/docs/derived-tables#use_cases_for_pdts) to improve query performance. Ensure your BigQuery Connection has enabled PDTs, then update any derived table syntax with the desired [persistence strategy](https://cloud.google.com/looker/docs/derived-tables#persistence_strategies).
100100

101-
- **Locale**: The Looker user [locale](https://cloud.google.com/looker/docs/model-localization#assigning_users_to_a_locale) setting (as seen in account profile) maps to SAP language code for _Materials_MD_, _Vendor Performance_, and _Inventory Metrics Overview_ views and determines material text language. See [language_map](views/language_map.view.lkml) for details.
101+
- **Locale**: The Looker user [locale](https://cloud.google.com/looker/docs/model-localization#assigning_users_to_a_locale) setting (as seen in account profile) maps to SAP language code for _BalanceSheet_, _Materials_MD_, _Vendor Performance_, and _Inventory Metrics Overview_ views and determines material text language. See [language_map](views/language_map.view.lkml) for details.
102102

103103
- **BI Engine Optimization**: Some calculations perform better with [BI Engine Optimization](https://cloud.google.com/blog/products/data-analytics/faster-queries-with-bigquery-bi-engine) enabled in BigQuery.
104104

105+
- **Liquid Templating Language**: Some constants, views, explores and dashboard use liquid templating language. For more information, see Looker's [Liquid Variable Reference](https://cloud.google.com/looker/docs/liquid-variable-reference) documentation.
106+
105107
- **(Optional) Unhide additional dimensions and measures**: Many dimensions and measures are hidden for simplicity. If you find anything valuable missing, update the field's `hidden` parameter value **No** in the relevant views.
106108

107109
<h2><span style="color:#2d7eea">Additional Resources</span></h2>

‎RELEASE_NOTES.md‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,7 @@
11
## Release v5.3
22
* Added views, explores and dashboards to support `Balance Sheet` reporting
33
* Added constants to the `manifest` to allow for the reuse of HTML value formatting and comparison period logic used in the Balance Sheet.
4+
* Note the manifest and the Balance Sheet views and explores leverage liquid templating language. For more information, refer to Looker's [Liquid Variable Reference](https://cloud.google.com/looker/docs/liquid-variable-reference) documentation.
45

56

67
## Release v5.0.1

‎explores_balance_sheet/balance_sheet.explore.lkml‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,9 @@ explore: balance_sheet {
66

77
always_filter: {filters:[balance_sheet.hierarchy_name: "FPA1",balance_sheet.chart_of_accounts: "CA01",balance_sheet.company_text: "%CENTRAL%",balance_sheet.target_currency_tcurr: "USD"]}
88

9+
# always filter on:
10+
# - Client as defined with constant
11+
# - if parameter select_fiscal_period is in the query, keep only rows where fiscal_period_group (Reporting/Comparison) is not null
912
sql_always_where: ${balance_sheet.client_mandt}='@{CLIENT}'
1013
{% if balance_sheet.select_fiscal_period._in_query %}
1114
and ${balance_sheet.fiscal_period_group} is not null

‎manifest.lkml‎

Lines changed: 34 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -1,20 +1,20 @@
11
constant: CONNECTION_NAME {
2-
value: "qa-thjennifer3"
2+
value: "cortex"
33
export: override_required
44
}
55

66
constant: GCP_PROJECT {
7-
value: "thjennifer3"
7+
value: "GCP Project Name"
88
export: override_required
99
}
1010

1111
constant: REPORTING_DATASET {
12-
value: "CORTEX_SAP_REPORTING"
12+
value: "Reporting Dataset Name"
1313
export: override_required
1414
}
1515

1616
constant: CLIENT {
17-
value: "100"
17+
value: "Client ID"
1818
export: override_required
1919
}
2020

@@ -43,39 +43,40 @@ constant: max_fiscal_period {
4343

4444
# comparison period derived based on select_comparison_type parameter:
4545
# if yoy then subtract year from period
46-
# if prior then subtract 1 from period (if period 001 then substract 1 year and use max_fiscal_period for period)
46+
# if prior then subtract 1 from period (if period = 01 or 001 then substract 1 year and use max_fiscal_period for period)
4747
# if custom then use value from select_custom_comparison_period
48+
# if none use ''
4849
constant: derive_comparison_period {
4950
value: "{% assign comparison_type = select_comparison_type._parameter_value %}
50-
{% assign fp = select_fiscal_period._parameter_value %}
51-
{% assign cp = select_custom_comparison_period._parameter_value %}
52-
{% assign max_fp_size = '@{max_fiscal_period}' | remove_first: '0' | size | times: 1 %}
53-
{% assign max_fp_size_neg = max_fp_size | times: -1 %}
54-
{% assign pad = '' %}
55-
{% for i in (1..max_fp_size) %}
56-
{% assign pad = pad | append: '0' %}
57-
{% endfor %}
58-
{% if comparison_type == 'custom' %}
59-
{% if fp == cp %}{% assign comparison_type = 'none' %}
60-
{% elsif cp == '' %}{% assign comparison_type = 'yoy' %}
61-
{% endif %}
62-
{% endif %}
51+
{% assign fp = select_fiscal_period._parameter_value %}
52+
{% assign cp = select_custom_comparison_period._parameter_value %}
53+
{% assign max_fp_size = '@{max_fiscal_period}' | remove_first: '0' | size | times: 1 %}
54+
{% assign max_fp_size_neg = max_fp_size | times: -1 %}
55+
{% assign pad = '' %}
56+
{% for i in (1..max_fp_size) %}
57+
{% assign pad = pad | append: '0' %}
58+
{% endfor %}
59+
{% if comparison_type == 'custom' %}
60+
{% if fp == cp %}{% assign comparison_type = 'none' %}
61+
{% elsif cp == '' %}{% assign comparison_type = 'yoy' %}
62+
{% endif %}
63+
{% endif %}
6364
64-
{% if comparison_type == 'prior' or comparison_type == 'yoy' %}
65-
{% assign p_array = fp | split: '.' %}
66-
{% if comparison_type == 'prior' %}
67-
{% if p_array[1] == '001' or p_array[1] == '01' %}
68-
{% assign m = '@{max_fiscal_period}' %}{% assign sub_yr = 1 %}
69-
{% else %}
70-
{% assign m = p_array[1] | times: 1 | minus: 1 | prepend: pad | slice: max_fp_size_neg, max_fp_size %}{% assign sub_yr = 0 %}
71-
{% endif %}
72-
{% else %}
73-
{% assign m = p_array[1] %}{% assign sub_yr = 1 %}
74-
{% endif %}
75-
{% assign yr = p_array[0] | times: 1 | minus: sub_yr %}
76-
{% assign cp = yr | append: '.'| append: m %}
77-
{% elsif comparison_type == 'none' %} {% assign cp = '' %}
78-
{% endif %}"
65+
{% if comparison_type == 'prior' or comparison_type == 'yoy' %}
66+
{% assign p_array = fp | split: '.' %}
67+
{% if comparison_type == 'prior' %}
68+
{% if p_array[1] == '001' or p_array[1] == '01' %}
69+
{% assign m = '@{max_fiscal_period}' %}{% assign sub_yr = 1 %}
70+
{% else %}
71+
{% assign m = p_array[1] | times: 1 | minus: 1 | prepend: pad | slice: max_fp_size_neg, max_fp_size %}{% assign sub_yr = 0 %}
72+
{% endif %}
73+
{% else %}
74+
{% assign m = p_array[1] %}{% assign sub_yr = 1 %}
75+
{% endif %}
76+
{% assign yr = p_array[0] | times: 1 | minus: sub_yr %}
77+
{% assign cp = yr | append: '.'| append: m %}
78+
{% elsif comparison_type == 'none' %} {% assign cp = '' %}
79+
{% endif %}"
7980
}
8081

8182
#} end additional constants

‎views/balance_sheet_rfn.view.lkml‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@
1212
# Languague
1313
# Global (Target) Currency
1414
#
15-
# Measuresr:
15+
# Measures:
1616
# Amount in Local Currency, Amount in Global Currency
1717
# Cumulative Amount in Local Currency, Cumulative Amount in Global Currency
1818
# Exchange Rate (based on last date in the period)

‎views/fiscal_periods_sdt.view.lkml‎

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -20,10 +20,11 @@ view: fiscal_periods_sdt {
2020
concat(b.FiscalYear,'.',{{fp}}) AS fiscal_year_period,
2121
parse_numeric(concat(b.FiscalYear,{{fp}})) * -1 as negative_fiscal_year_period_number
2222
FROM `@{GCP_PROJECT}.@{REPORTING_DATASET}.BalanceSheet` AS b
23-
group by FiscalYear,
24-
FiscalPeriod
25-
26-
23+
group by fiscal_year,
24+
fiscal_period,
25+
fiscal_year_quarter,
26+
fiscal_year_period,
27+
negative_fiscal_year_period_number
2728
;;
2829
}
2930

0 commit comments

Comments
 (0)