Hello GoodData team 😃 Writing to ask about any ideas/suggestions on how I can achieve the following. Thank you so much in advance! I created a metric that calculates a running sum (cumulative totals). I need the metric to calculate over the entire dataset to maintain cumulative totals, but the time frame on the visualization should be displayed based on the selected date filter (CREATED_AT) while keeping the cumulative sum of the complete data set. Here's the metric I'm using:

SELECT
   (runsum({fact/AGG_DAILY_EVENTS.INS_CREATED}) -
    runsum({fact/AGG_DAILY_EVENTS.INS_DELETED}))
    with PARENT FILTER EXCEPT {label/CREATED_AT.hour}

However, with the above metric I'm excluding the date filter (CREATED_AT) from my visualization which is not the desired outcome 😐 Any ideas how I can achieve what I have in mind? Requirements: 1. The metric should always calculate the running sum using the entire dataset, regardless of any date filters applied on the dashboard. 2. When a user applies a date filter on the dashboard, this should not alter the computation of the metric but should only affect what portion of the cumulative total is displayed.

Hi Ana, happy to help here. I am not sure if I understood correctly. I have tested in my end and try to reproduce what you need. First I created exactly the same metric you proposed without filtering for any date, just to see the 'sum' of all days, and I get the first attached image. Then I select as filter a range of days. For example from 8 days ago to 6 days ago (same with range of dates) . As you can see the computation of the metric is the same. But, when you say

this should not alter the computation of the metric but should only affect what portion of the cumulative total is displayed.

you mean, actually, the values you expect are NOT the same as the showed without filter? in that case you expect the sum ONLY taking into account the filtered date, right? In that case need to ensure that the date filter is applied at the visualization level (as an attribute filter), not within the metric itself. Here's the correct MAQL that will give you the result you're looking for (please, confirm):

SELECT
   (runsum({fact/ins_created}) -
    runsum({fact/ins_deleted}))

Could you please check?

Hi @Mauricio Cabezas 👋🏾 Thank you so much for your input !! 😃🙌🏾 Let me try to explain better, I actually need the opposite to what you mention here

"the sum ONLY taking into account the filtered date"

. What I need is to show the running/cumulative sum including all previous dates Hopefully this image explains it better. To give an example, I need a visualization that gives me the info on the purple box (assuming the user selected dates from week 39 to week 53) where the cumulative sum considers also the sum of prior values that may be excluded by the date filter. (see first image) However, what I’m getting with the metric below is the second image. I’m expecting that the initial count should be around 15M but it looks like the metric recalculates the cumulative sum based on the period selected

SELECT
   (runsum({fact/ins_created}) -
    runsum({fact/ins_deleted}))

Hi Ana, thank you for your update. In other words, you need the RunSum from the day zero (or first Date value) until the selected filtered day, but showing only the date range? In that case, isn't the first metric you provided is the correct one? as you can see in my previously attached images (light blue columns) I used the following metric.

SELECT
  (runsum({fact/ins_created}) -
    runsum({fact/ins_deleted}))
    with PARENT FILTER EXCEPT {label/CREATED_AT.hour}

You can see, I filtered by the dates under the red rectangle.

Hi @Mauricio Cabezas thank you for all your help ! Yes, I would expect the metric I'm using to provide the expected result as you describe, however, for some reason is not working for me. I'll do some more tests to figure out the issue !! thank you again for your help 🙌🏾 😃

Hi Ana, thank you for your update, is interesting. In your example, just to be sure, the view is by Month and you filtered by week, right? You are close with your last question, GoodData's date filters generally work best when the filter granularity matches or is larger than the insight's granularity. This is because GoodData aggregates data according to the insight's granularity. For example, if your insight displays data aggregated by Month, and you apply a date filter that selects specific week, you might encounter unexpected results. The filter might not accurately reflect the weekly aggregation. So is not about apply the filter by the same granularity of the Insight, but must be with larger granularity. I am sorry if is not working as you expect. Please, let me know if need more assistance.

Hi Mauricio, Hope you are well! Thank you for your help so far 😃 I’ve been doing several tests and I think there might be an issue with https://www.gooddata.com/docs/cloud/create-metrics/maql/filters/overriding-parent/with-parent-except/ I’m giving some examples in the images attached where using the below metric, the parent date filter only works on the visualization when using ‘week’ in the ‘view by’ from the visualization; but doesn’t work with any other dimension. Even in cases where the date period in parent filter is larger than the granularity from the visualization (e.g., month, year, quarter, etc.) When using the same date period it does work, for example weeks and weeks on both filters but not for the rest Metric

SELECT
 (runsum({fact/AGG_DAILY_IN_EVENTS.IN_CREATED}) -
 (runsum({fact/AGG_DAILY_IN_EVENTS.IN_DELETED}))) WITH PF EXCEPT {label/CREATED_AT.day}

• Do you also see this issue on your end? Thanks again for your help 😃🙌🏾

Hi Ana, I hope yo udon't mind me stepping in here. However, I believe the clause:

WITH PF EXCEPT {label/CREATED_AT.day}

is blocking the filter from affecting the metric. This clause essentially states that the metric will work with all parent filters, except the CREATED_AT date filter.

Hi @Joseph Heun 👋🏾 and @Mauricio Cabezas Thank you for your input! 🙌🏾 From what I understood, the clause

WITH PF EXCEPT {label/CREATED_AT.day}

is expected to exclude the filter from the metric (i.e., the calculation) but should not exclude the parent filter to be applied on the visualization. Actually, the functionality works as expected in some cases (as can be seen in my image) but not for all as also shown on the examples I shared above. Hope this brings a but more clarity to the issue 😃

Hi Ana, could you please provide a link to the report so we can check this out in your environment? You can DM if you'd like

Hi Ana, as my colleague Joseph says and is stated in the documentation on WITH PARENT FILTER EXCEPT

Includes all parent filters, except for any filters specified as a comma-separated list of attributes.

The metric is taking into account all the filters but NO the specified filter in the metric. In your case would not matter how you filter Created at because will not be taking into account in the metric and calculation if you have the

WITH PARENT FILTER EXCEPT

expression in there, s you can see in my example. In there I have three metrics; Light Blue: With EXCEPT CREATED AT -> will consider only the filter

Id=All

, thus I will see all the records. Light Green: without EXPECT CREATED AT -> that means it will take into account Created at filter, thus you can see only the data for This Quarter, as expected. Red: With EXCEPT ID -> will take into account the

Created At

filter, and not considering the

Id=All

filter, but still is taking All, because is the default value. I hope this explanation helps to understand better how works the

WITH PARENT FILTER EXCEPT

expression.

Hi @Mauricio Cabezas and @Joseph Heun Thank you so much for your detailed explanations, I think we are on the same page in that

WITH PARENT FILTER EXCEPT

will omit the effect of the created_at filter in the metric/calculation ✅ However, I was expecting the

created_at

filter to affect the timeframe I see in the graph/insight/visualization (in other words, timeframe of x axis). It does work in some cases as you can see in the images I provide, see images A, B, and D but it doesn’t work for other cases see images C and E. 1. What is the reason for the functionality to work as I expect in some cases but not in others? In one of our previous messages it was described that it would work when “the filter granularity matches or is larger than the insight's granularity.” However it is not the case, as it can be seen on images C, and E The metric I use in all examples is

SELECT
 (runsum({fact/AGG_INS.IN_CREATED}) -
 (runsum({fact/AGG_INS.IN_DELETED}))) WITH PF EXCEPT {label/CREATED_AT.day}

Thank you once again for all your support so far; it has been very helpful for me ! 😃👍🏾

Hi Ana, thanks once more for your update. I think this time it would be needed to see the issue, in that case, as my colleague Joseph asked before, could you please share with us the link to the insight in order to impersonate and investigate further? Please, feel free to use DM.

Hi @Mauricio Cabezas thank you so much for following up on this! 🙌🏾 1. Can you give me a bit more details on what you mean by the visualization link? for me to understand better where/how to get this link and how you could access our environment. 2. We also use analytics as code so maybe I can share with also the visualization definition? (i.e, the code file) would this help as well? 3. On your end, do you not see the same behavior when trying to reproduce the same metric with the different granularities I shared above? Thanks again !! cc @Zuzana Vranova

Hi Ana, with visualisation link, I mean the URL is in your browser when you are editing the Visualisation, or you can provide the Dashboard/Workspace ID and the Name of the Insight. For now with this would be fine. I am not able to replicate, but maybe I am missing something, for that reason I would like to check it. Thank you and feel free to use DM.

Hi @Mauricio Cabezas, sent you a DM with the link

Thank for share it, I will check.

Hi @Mauricio Cabezas thank you for checking!! 🙌🏾 Please keep us updated 👍🏾

Hi Ana, thank you for your patience while I consulted with our engineering team. The behavior you’re seeing is related to how MAQL processes filters when using RUNSUM together with WITH PF EXCEPT. When you group by Week and try to filter by a different Date attribute like Day, the filter doesn't behave as expected because of how the data is structured under weeks. For example, a single Week may contain days from two different months or even two different years. So, when you filter by Year or Month, the filter may exclude some days but the Week aggregation will still include all days, causing inconsistencies in the visualization. This is why the metric works correctly when grouping by broader periods like Year or Month, but breaks when grouping by Week. Our engineers also confirmed that attributes used in the breakdown (like Week) cannot be excluded from the filter propagation using WITH PF EXCEPT. There are advanced techniques to apply filters at different stages (WHERE, HAVING, or nested metrics), but this specific use case is limited by how MAQL currently handles partial filter exclusion combined with date hierarchies. I am really sorry for this. I hope this could help.

Hi @Mauricio Cabezas, thank you for the information!