Hello! We're hoping to use the MAQL Running Total Functions (specifically,

RUNSUM

) to show totals in Tables across columns, rather than across rows. An example of what we're trying to achieve:

|     01/01/2023     |     02/01/2023     |     03/01/2023     |
              | This Month | Total | This Month | Total | This Month | Total |
______________________________________________________________________________
Thing A       |          5 |     5 |         10 |    15 |         50 |    65 |
______________________________________________________________________________
Thing B       |          1 |     1 |          7 |     8 |          7 |    15 |
______________________________________________________________________________
Thing C       |         50 |    50 |         10 |    60 |         12 |    72 |
______________________________________________________________________________

Does anyone know if something like this is feasible, or have suggestions for other ways to accomplish it? I have been able to get this to work in the other direction, with the dates as rows and the monthly values + totals for Things A, B, and C in different columns, but would really like to be able to have the dates in columns instead. We have a lot more "Things" than we do dates, and so having the "Things" as the rows is preferred by our users. Thank you!

Hello Maia, This will depend on how you sort the values on your table, as you stated, it is possible to shift attributes between rows and columns, although I am not sure if there is a way to make it work by placing the date in columns instead. It would be useful to know how you have defined your metric. Note that your metric will have to defined correctly so it can return the values within the correct attribute, e.g.,

SELECT RUNSUM({fact/sales}) WITHIN({label/date.week}

Thank you for the response, Moises! This was a simplified example -- we're actually doing some additional math within the custom metric for which we'd like to leverage the

RUNSUM

(and other running total functions) in order to take into account the "previous" values, so we can't use the UI to do the summing. However, using the

WITHIN

clause may be what we need to make this work - thank you for that tip! I'll give it a shot and supply more details if that doesn't work for us. Thanks again!

The running total functions currently work only over window defined by date attributes. I will record this as a product feedback. May I ask you more details about the ultimate use case you want to achieve? • Do you want to display column in table with cumulative value which will depend on the order of rows so it will recalculate for example when user changes order of the “things” from ascending to descending? • Or do you want to use it as part of MAQL expression with predefined order of the “things” as part of some advanced metric where cumulative sum is only part of more complex formula which will not recalculate when ordering of table is changed by user? • Or do you need something else?

🎉 New note created.

Hi @Jakub Sterba! Thanks for the information. We are hoping to use it as part of a MAQL expression, with the order defined by the left-to-right order of the columns (which can't be changed by the user). In other words, the order of the rows doesn't actually matter to the value. The running total for column 1 would be the same as the value of the metric against which which the running total is being applied (e.g. "Number of Units Sold"). The running total for column 2 would be determined by adding the value of that metric in column 1 to the value of that metric in column 2, and so forth. Basically, take the first example at https://www.gooddata.com/docs/cloud-native/3.3/create-metrics/maql/runing-totals/#RunningTotalFunctions-Examples and move "Date" to be a column. Then, in this example, Units, MTD, and YTD are the metrics being displayed, "Date" is the attribute being used for the columns, and a hypothetical "Item Name" attribute would be the rows. (i.e. you would see the units / MTD / YTD values for each item, with a series of dates as the columns.) Hopefully that makes sense? Let me know if you have any other questions, and thank you for recording this feedback!!

What you describe seems feasible. The RUNSUM function adds value with each additional element of the single date dimension in the insight, regardless if it is displayed as rows or columns. Other attributes in insight are used to define partitions within which the cumulative sum is computed. See example:

Hmm, thank you Jakub! I'll put together an example on our side and see if I can figure out why that wasn't working for me when I tried it. (We're using CN rather than cloud, but I don't think it matters, yes?) Thank you for the help!