Hi all! Got a question regarding MAQL on GDCN: Sa...
# gooddata-cn
Hi all! Got a question regarding MAQL on GDCN: Say I have table with data:
Copy code
project_id, device_id, datetime
1, 543, 2021-01-21
1, 543, 2021-02-21
1, 543, 2021-03-21
1, 123, 2022-01-22
1, 123, 2022-02-22
1, 456, 2022-03-22
("datetime" field can be either a date object, or an attribute.) Assume today is 2022-03-31 And I have a date filter applied of last 90 days. Questions: 1. How can I write metrics that select min and max date within a period? Ultimately, striving to produce a 'Table' or other insight to show:
Copy code
min_previous_period, 2021-01-21
max_previous_period, 2021-03-21
min_this_period, 2022-01-22
max_this_period, 2022-03-22
2. Does GDCN have any mechanism to adjust this-period and previous-period to ensure match Day of Week as closely as possible?
@Petr Olmer would you know?
Hi Carson, regarding question #1 You would need to add a new fact that represents the date, for example the unix timestamp. You would then use this new fact in the MAQL for example:
select min({fact/datenum});
Regarding your 2nd question, What exactly did you need to adjust? You can add a second parameter to the time macros to shift the time window manually, for testing for example
Thank you David! For #1 that solves it -- created INT field (as can't create facts from date-fields), and did
formatting. Is there any way to convert this e.g. '20220311' to different date formats e.g. 'Fri Mar 11, 20202'? For #2 I am doing Year Over Year calculations, and so want to compare e.g. today to last year +/- the required number of days so that I am comparing a Friday to a Friday.
Fri march 11, 2022
- 1year =
Thursday march 11, 2021
So instead would want to do -1year +1 day to account for the Day of Week change.
So maybe you want to work with Week instead of Year granularities. Try using
FOR PREVIOUS({week attribute}, 52)
👀 1
Is there any way to convert e.g. '20220311' to different date formats e.g. 'Fri Mar 11, 20202'? The by-week is useful, but we still need a YOY by-same-day-of-week. This seems achievable with a bunch of MAQL to get the 'day of week' offset, but could be useful to breakout into a MAQL helper function.
Re formats: I assume you mean date formats for the display in Analytical Designer. Currently that's not possible. cc @Martin Svadlenka
👀 1
👍 1
Re YOY by-same-day-of-week: I probably don't understand. Does my solution with
FOR PREVIOUS({week attribute}, 52)
solve your issue? Because that works exactly as you seem to want, i.e. if today is Friday that construct wil give the Friday closet to one year ago.
Thanks David, this is helpful. One need I'm seeing repeatedly is ability to "select SUM({fact}) WHERE date = MAX({day date attribute})". Instead, I only see ability to reference date logic in relation to "today". It would be useful to do logic scoped relative to whats in the dataset. Can see this issue causes your demo here to show null primary metric: https://gdui-examples.herokuapp.com/time-over-time-comparison . To rephrase -- we want to do time logic relative to the dataset, rather than relative to "today". E.g. "show me sum of X for the last 90 days _in the dataset_".
In the GD-ui demo link above, it appears it doesn't have recent data, and so the logic (relative to "today") scopes a time window with no datapoints.
I think I understand now:
show me sum of X for the last 90 days in the dataset
This is currently not possible in GD.CN. Note however, that the
Copy code
Total Sales - period ago
measure dynamically reacts to a filter you could have set up for your users in your app. The Heroku example is obviously simplified and therefore doesn't work well precisely because there's hardcoded
which is not what you should do in you interactive app.