Hi, I'm trying to create a metric which returns the average Quarter to Date value for a numeric column. Jan-24 Feb-24 Mar-24 Apr-24 Sector Cost Cost Cost Cost Consumer, Non-Cyclical 100 102 103 106 Consumer, Cyclical 105 107 101 104 Technology 104 109 104 102 I've tried to use Case When to do the logic. However I seem to be running into an issue where only one column will populate. Any advice? I tried something like this: Select case when date.monthOfYear in ("01", "04", "10") then sum(Cost) else when date.monthOfYear in ("02", "05", "11") then select sum( sum(Cost) + sum(Cost) for PreviousPeriod(date.monthOfYear) )/2 else select sum( sum(Cost) + sum(Cost) for PreviousPeriod(date.monthOfYear) + sum(Cost) for PreviousPeriod(date.monthOfYear,2) )/3 END

Hi Alex! From what you’ve described, it looks like you could achieve this result by using one of our Running Total functions: the Running Average (RUNAVG). From your scenario, you’d be looking for something like this:

SELECT RUNAVG (Cost) WITHIN (date.Quarter)

This will calculate the running average Cost per Quarter, which you can then slice by a Monthly Date dimension on the Analytical Designer; the WITHIN keyword will ensure that the calculation is contextualized by Quarters. I would also recommend checking out the article Running Total Function in GoodData.CN (Sum vs Runsum) for more details about the Running Total function and how it can be used 🙂

Thank you Francisco! I will give that a try!