Metric to count periods

Hello Renaud,

Thanks for reaching out with this question. It was very neatly laid out, and the example Table illustrated perfectly what you’re hoping to achieve here! In fact, the logic behind the query you wrote is quite accurate to get these results - we only need to adapt the syntax to work with MAQL.

The solution involves utilizing the MAX Function in combination with the Date dimension that you’ll use to filter out your Insight. It’s explained in detail in the article Finding Min and Max Dates Using Date Arithmetic, but here’s an example solution for your use-case:

You’d start by creating a couple of metrics that you’ll use to define MAX(Date) and MIN(Date) - which, when used with a Date Filter, will result in the last date on the filter and first date on the filter, respectively. It’d be something like:

SELECT MAX (Date(Registration_End))

 and

SELECT MIN (Date(Registration_End))

We’re using Date(Registration_End) because that’s the dimension that really affects the logic - even when the Registration Start is used, it must be compared with the End date, in this logic. Then, you’d build the main metric using these two as sub-metrics. Something like:

SELECT COUNT (Users) WHERE Date(Registration_end) >= Min_date_metric OR Date(Registration_end) = (empty value) AND Date(Registration_end) <= Max_date_metric

As you can see, it’s pretty much the same logic as you used in your example; The main difference is that we used MAX and MIN sub-metrics to incorporate the filter into the main metric. Please read the article I shared for more details, give that a try, and let us know how it goes!

Best regards,

Francisco Antunes - GoodData Support

Hi Renaud,

Thanks for the reply. You are right, you’re trying to compare more than one Date dimension - in this case the solution would need to be adjusted a bit.

In order to ensure the filter applies to the multiple date dimensions in the Report, it would be necessary to set up a Disconnected Date Dimension. The article I linked explains it in more detail. In short, the goal is to create a separate Date Dimension on your LDM that is not connected to any date; Then, you’d adjust the MAX and MIN Date metrics so that they apply to that new dimension instead, in the way suggested in the article so that it will not be affected by the other date dimensions.

Then, incorporate those sub metrics into the main metric, just like in the previous solution; Finally, you’d add a Date Filter for this disconnected dimension on the Dashboard/Insight using the metric, and use it to limit your dates. This way you can compare both Registration_start and Registration_end with the filtered date.

Please read the article I shared and try performing this adjustment, and let us know how it goes!

Best regards,

Francisco Antunes - GoodData Support

Hi Renaud,

If you have other metrics in the same insight, the filter will not apply to them. That’s one of the limitations of this method, sadly: the disconnected date dimension must be added to each metric manually, via the sub-metrics we discussed. This is most likely the reason why you are unable to add if from the Analytical Designer - it does not consider the Dimension as valid for the insight. But good job on forcing it via the API, that’s a nice workaround. Originally, this solution was intended to be used with the old Report Builder, which was a bit more flexible when it came to filtering (although not as snappy or easy to use as the Analytical Designer).

I understand this may be a lot of work and could cause the solution to become unwieldy. But this is the only way of comparing multiple different date dimensions in GD Platform within a metric/insight.

Best,

Francisco Antunes - GoodData Support