Hello everyone, I have a simple business case and I’d like some advice on the best way to model it in GoodData. Let’s say my LDM is about items purchased online, and each item can be in one of three states: • Purchased • In Shipping • Shipped I want to be able to see the number of items that were in the

In Shipping

state at the beginning of each month.For example, to see the number of items currently

In Shipping

on 01/06/2026 (June 1st), a working logic might look like this: Metric A: Rank the events of an item in descending order capped by June (allowing me to retrieve the state of the last event before that date by doing looking for the event with the 1 value).

SELECT ROW_NUMBER(Epoch) DESC WITHIN(ItemId) WHERE Month/Year (EventDate) < June 2026 WITHOUT PARENT FILTER

Metric B: Compute the number of items that were in the 'In Shipping' state right before 01/06/2026.

SELECT COUNT(ItemId) BY ItemId, EventId WHERE MetricA = 1 AND ReportingState = 'In Shipping'

However, I would like to know if there is a way to compute this metric dynamically for every month of a year. Right now, the only solution seems to be duplicating the metric for each month, which isn't scalable. At first, I thought I could simply slice this metric by

Event Date (Month/Year)

in the Analyze view, but this does not work. For example, if I take the metric above and do that, it still returns the number of items in the

In Shipping

state on 01/06/2026, but with the information split by the month they originally entered that state. Which is not what I want Does anyone have a better approach to handle this dynamically? Thanks in advance!

I'm sorry, but the knowledge sources do not contain enough information to fully address your specific use case. Your scenario involves a slowly changing state problem — you want to determine, for each month boundary, what state each item was in at that point in time, and then aggregate across all items. This is a non-trivial temporal pattern that goes beyond what the available MAQL documentation covers. Here is what the knowledge sources do confirm, along with their limitations for your case: What MAQL Can Help With Time-based filtering per metric — You can use

FOR Previous

,

FOR PreviousPeriod

, or time macros (

THIS

,

PREVIOUS

) to reference specific past periods. [FOR Previous] [Time Macros] Metric-level date filters — You can add a "Filter by date" to a metric instance in a visualization to scope it to a specific period. [Filter by Date] BY / ALL OTHER clauses — These let you lock a metric's aggregation grain independently of the visualization's breakdown attributes. [Community Slack] Why Your Use Case Is Difficult in Native MAQL The core challenge is that your Metric A needs to be evaluated relative to a dynamic, per-column date boundary (e.g., "before June 1", "before May 1", etc.) as the visualization iterates over months. MAQL does not appear to support this kind of correlated subquery pattern where a filter inside a metric references the current row's date context from the visualization. The knowledge sources explicitly confirm that filters are applied uniformly to a metric — you cannot make a filter condition vary row-by-row based on the visualization's current attribute value. [Community Slack] Recommended Approaches Based on what the knowledge sources support, the most practical options are: 1. Precompute in ETL/data pipeline: Add a column to your dataset that, for each item and each month, records the state of the item at the start of that month. This "snapshot" table can then be queried simply with

COUNT(ItemId) WHERE State = 'In Shipping'

sliced by month — no complex MAQL needed. 2. Separate metrics per month: As you noted, duplicating the metric per month works but is not scalable. The knowledge sources confirm this is a known workaround for similar problems. [Filter by Date] The ETL/preprocessing approach (option 1) is the most robust and scalable solution for this type of point-in-time state reporting, and is consistent with the guidance given for similar dynamic aggregation challenges in the knowledge sources. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups. 👀 Our Support team monitors all messages and will jump in if needed.

Hi Lyes, Jumping in here. Our internal AI is using some wording from our legacy product (e.g. "ETL/pipeline"), but its direction is generally right. The core issue: slicing by

Event Date

won't give you what you want, because

Event Date

is when the state changed, not the reporting/as-of date you're trying to report on. Whenever the question is "what state was each item in at this point in time?", the answer needs its own snapshot/as-of date in the model rather than reusing the event date. We've solved similar cases this way before. In GoodData Cloud this does not require an ETL pipeline. You can model it as a SQL Dataset in the LDM, defined by a SELECT query that acts like a virtual view. Have that query return one row per

Item

+

month-start date

, carrying the item's latest state before that month start.apshot Month. One thing to keep in mind: a SQL Dataset isn't materialized. GoodData re-runs the query every time it's used and always returns the full result , so an items × months cross join can get expensive. If performance matters, backing it with a materialized view/table in the source is the more efficient route, but the SQL Dataset is the quickest no- "source data change" way to validate the approach.