Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Fiscal Year Calculation

  • 5 July 2016
  • 4 replies
  • 1157 views
srivera

In Looker 4.8, we introduced new functionality for fiscal years and quarters. You can now define a fiscal_month_offset LookML parameter to allow for custom fiscal years instead of using the technique described in this older article. Learn more



Is there a pattern for block for calculating a Fiscal Year that is different for the calendar year? I have a client that want dashboards to conform to their fiscal year which ends on September 30.


So,

Oct 2015 - Dec 2015 = Q1-2016

Jan 2016- Mar 2016 = Q2-2016

Apr 2016 - Jun 2016 = Q3-2016

Jul 2016 - Sep 2016 = Q4-2016

Oct 2016 - Dec 2016 = Q1-2017


Thanks in advance,


Sonny

4 replies

L

Hey Sonny,


I would do this in the lookml, with a case statement in the sql param

srivera

I came up with this



Old LookML
```
- dimension: fiscal_quarter
sql: |
CASE
WHEN ${date_month_num} in (1,2,3) then 'Q2' + '-' + CAST(${date_year} AS VARCHAR)
WHEN ${date_month_num} in (4,5,6) then 'Q3' + '-' + CAST(${date_year} AS VARCHAR)
WHEN ${date_month_num} in (7,8,9) then 'Q4' + '-' + CAST(${date_year} AS VARCHAR)
WHEN ${date_month_num} in (10,11,12) then 'Q1' + '-' + CAST( (${date_year} + 1 ) AS VARCHAR)
ELSE
NULL
END
```


New LookML
```
dimension: fiscal_quarter {
sql: CASE
WHEN ${date_month_num} in (1,2,3) then 'Q2' + '-' + CAST(${date_year} AS VARCHAR)
WHEN ${date_month_num} in (4,5,6) then 'Q3' + '-' + CAST(${date_year} AS VARCHAR)
WHEN ${date_month_num} in (7,8,9) then 'Q4' + '-' + CAST(${date_year} AS VARCHAR)
WHEN ${date_month_num} in (10,11,12) then 'Q1' + '-' + CAST( (${date_year} + 1 ) AS VARCHAR)
ELSE
NULL
END
;;
}
```
tig
Rank

For each old LookML code block in this article and its comments, we just added the New LookML equivalent code.

tig
Rank

I just added a note at the top of this article about our new functionality for fiscal years and quarters. Starting in Looker 4.8, you can now define a fiscal_month_offset LookML parameter to allow for custom fiscal years instead of using the technique described in this older article. Learn more

Reply


Terms of Service