Hello! I'm hoping to get some help creating a metric that I'm struggling with. If I have a Product Sales data set such as:

| product name | sales date |
=============================
| widget 1     | 2024-01-01 |
| widget 1     | 2024-02-01 |
| widget 2     | 2023-03-02 |
| widget 1     | 2024-07-01 |

I need to calculate a metric that can be viewed by month, quarter or year that shows the percentage of products ( by product name) that were sold in that period and were also sold in the previous period. I'm having a hard time figuring out how to formulate the sub-selects I will need to find the count of products that are in both this period and the previous period. Any help would be appreciated, thanks!

Hi Mitchell, to find the values for this period and the previous period, I would like to suggest that you review the Time macros documentation - Time macros or time macro functions are “floating” references to day, month, week, and other date-related attribute values, relative to the present. MAQL supports the following time macro functions: • THIS • PREVIOUS • NEXT These macros are useful for creating visualizations that relate to data from “today”, “yesterday”, “this month”, “next year” etc. More details can be found here: https://www.gooddata.com/docs/cloud/create-metrics/maql/time-macros/

Hi Michael, thanks for the response. I am clear on using THIS() and PREVIOUS(); however, I am not grasping how I would write the MAQL so that I can find the count of product names that fall into those buckets. I think it would be something like this but I can't get the syntax correct:

SELECT (COUNT(product_name) WHERE (product_name IN (SELECT product_name WHERE sales_date.day = THIS(DAY)) AND product_name in (SELECT product_name WHERE sales_date.day = PREVIOUS(DAY))

The IN doesn't seem to accept a SELECT statement and I'm not sure how else I would form this metric

Hi Mitchell, you’ll need to create 2 metrics and then combine those metrics to create your percentage metric to compare the 2 periods - For example something like this:

SELECT (SUM([Metric A])) / (SUM([Metric B]))

Your metrics would look something like this:

Select COUNT ({label/product_name}) WHERE {label/sales_date.day} = THIS(DAY)

Select COUNT ({label/product_name}) WHERE {label/sales_date.day} = PREVIOUS (DAY)

And you’d then combine those metrics in a 3rd like this:

SELECT Metric Year = This / Metric Year = Previous

We have this legacy article that helps explain how to calculate and compare percentages which you might find useful - The images look different and the build of the is slightly different in look when using GD Cloud. However, the MAQL logic is the same: https://help.gooddata.com/classic/en/dashboards-and-insights/reports/working-with-tables/calculate-percentage-shares/

Great. I'll look into that. Thanks again

Hi Mitchell, I am afraid that it is not possible to define such metric in MAQL since the IN is for enclosing label values; I would recommend creating two different metrics. Have you also considered using time over time comparison: https://www.gooddata.com/docs/cloud/create-visualizations/filter/time-over-time/?

Hi Moises, and thanks, but I don't think that helps in this case. I'm just trying to determine if there is a way that I can write a metric that will show me the number of product names that are contained in the intersection of product names for this period and product names for the previous period. I can get those counts individually using FOR and FOR PREVIOUS but without some sort of an IN function, I don't know how to find the intersection of the two sets.

Hi Mitchell, if I understand correctly what you are asking for, you need a metric that will give you a number of products names that were sold BOTH this period and previous period (and not just current period, but for each period in the table). Is that correct? (I know you actually want to get a percentage, but to be honest I am not sure what should be the base of it, so I will at least try to help with this one). I believe this metric can do the trick:

SELECT COUNT({label/product_name},{dataset/sales})
WHERE
    (SELECT COUNT({dataset/sales}) BY {label/product_name} FOR PREVIOUSPERIOD({label/sales_date.day})) > 0
AND
    (SELECT COUNT({dataset/sales}) BY {label/product_name}) > 0

The IN logic you were looking for is actually achieved by the sub-metrics dimensionalities. (when the sub-metric in the condition has forced dimensionality with the BY, it hints the system that we want to apply the filtering on that granularity - on product_name in this case) The metric looks a bit complicated but the logic is not that difficult. When rewritten to more human-readable form it would be:

SELECT COUNT(product) WHERE sales_by_product_normal>0 AND sales_by_product_previous_period>0

and the

sales_by_product_...

logic is done by counting the sales records. And the FOR PREVIOUSPERIOD shifts data in the submetric by one date period. What period it is, is dynamic and depends on the date granularity used in the visualization. Note that this works without IFNULL, because the condition is for existence of the sales records. If you on the other hand wanted to look for products sold in this period but not sold in he the previous period, you would need to put IFNULL around the sub-metrics… I hope this helps.

Hi Michal. That is what I was trying to get, thank you very much! Using the BY with the AND was the trick I was looking for. Appreciate the help from everyone in this thread