Open MadhusudanN opened 9 months ago
Hi @MadhusudanN 👋 Thanks for a very detailed and thorough question!
So I notice the Cube UI is the one which is handling the missing data and showing it as 0 where data is missing from the backend. Can some one please confirm on this behaviour.
Your conclusion is exactly correct. Let me explain what is happening here:
fillMissingDates
option of PivotConfig
). Such defaults generally makes sense because embedded analytics applications usually put data on charts. There's also an option to override these defaults with fillMissingDates: false
.I hope it clarifies things.
Is it something that renders Playground unusable for you?
Hi @igorlukanin Thanks a lot for the reply. It is clear. I have one question regarding the support for Fiscal Year, Fiscal Quarter etc. in the time dimension. For example if we need to get sales total based on Fiscal Quarter, do we have support for that in cube. Please see the screenshot below for illustration.
Can you please let me know is there a way we can achieve this with Cube.
@MadhusudanN Sure! Just FYI, there's an open discussion about fiscal year support in date calculations that you can join.
However, in your particular use case, a very viable and working approach would be to define a separate dimension for the fiscal quarter and use it in your queries. Consider the following data model that defines such a dimension for Australia, where FY2024-Q1 starts in July 2024:
cubes:
- name: fiscal
sql: >
SELECT '2024-01-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
SELECT '2024-02-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
SELECT '2024-03-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
SELECT '2024-04-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
SELECT '2024-05-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
SELECT '2024-06-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
SELECT '2024-07-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
SELECT '2024-08-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
SELECT '2024-09-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
SELECT '2024-10-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
SELECT '2024-11-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
SELECT '2024-12-15T00:00:00.000Z'::TIMESTAMP AS timestamp
dimensions:
- name: timestamp
sql: timestamp
type: time
- name: fiscal_year_internal
sql: "EXTRACT(YEAR FROM {timestamp} - INTERVAL '6 MONTH')"
type: string
public: false
- name: fiscal_quarter_internal
sql: >
CASE
WHEN EXTRACT(MONTH FROM {timestamp}) BETWEEN 7 AND 9 THEN 1
WHEN EXTRACT(MONTH FROM {timestamp}) BETWEEN 10 AND 12 THEN 2
WHEN EXTRACT(MONTH FROM {timestamp}) BETWEEN 1 AND 3 THEN 3
WHEN EXTRACT(MONTH FROM {timestamp}) BETWEEN 4 AND 6 THEN 4
END
type: string
public: false
- name: fiscal_quarter
sql: "'FY' || {fiscal_year_internal} || '-Q' || {fiscal_quarter_internal}"
type: string
Here's what you'll get querying the fiscal_quarter
dimension:
Note that fiscal_year_internal
and fiscal_quarter_internal
would need to be adjusted to the fiscal calendar that you'd like to use because different fiscal calendar schemes use different month offsets.
I hope it helps 😊
FYI, I've extracted my comment into this recipe: https://cube.dev/docs/guides/recipes/data-modeling/fiscal-year-quarter-dimensions