Hi all! Got a question regarding MAQL on GDCN: Say I have table with data:

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:

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});

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)

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

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_".

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

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

newRelativeDateFilter

which is not what you should do in you interactive app.