for time macros and functions, there’s a

This

, previous, previousPeriod, next and nextPeriod… is there any way to get the value of the first period so i can use it as the divisor for subsequent periods? So if i have a monthly report for example, i’d have 12 values, X1-12, then i want to calculate a value from it, so in january, it’ll be X1/X1, then February X2/X1… X12/X1

Hi Hunger, it seem to me that you can use MIN function to find the first value together with ALL OTHER clause to lock the aggregation level and use the metric in your calculations. If this doesn’t suit your needs feel free to elaborate.

aaaah… ok let me give that a shot, tried min/max and couldn’t figure out what i was missing

The help article above is for hosted version of GoodData. The solution which should work in GD.CN/Cloud is:

select {metric/x}/(select {metric/x} by all other where {label/date.month}="2022-01")

the first value is however not determined dynamically. The inner select will compute value of X for January and will use the same value for each month as denominator of the division. The next release will deliver improvement. After this release you should be able to use following MAQL statement:

select {metric/x}/
   (select {metric/x} 
     by all other 
     where {label/date.month}=
     (select min( {label/date.month}, {dataset/facts}))
   )

The

select min( {label/date.month}, {dataset/facts})

will find the first month in the Facts dataset (the slicing by month will be not applied because it is used to filter metric with BY ALL OTHER). You can test the second formula on

gooddata/gooddata-cn-ce:dev_latest

already. We are considering to introduce function FIRST_VALUE which would simplify the formula a bit.

aaaa awesome, glad to hear you’re already ahead of this 😄