I made some progress with my previous question, bu...
# gooddata-platform
I made some progress with my previous question, but here is a follow up: I am trying to build a table that derives the balance of an account at the beginning and end of a month (month is an example, this should also work on any other time granularity). Given is a table with transactions and each transaction has a balance before and after the transaction, Transactions have a transaction number that is increasing but can have gaps (similar to a rank). I already managed to build metrics that compute the last transaction before a month and the first transaction after a month. Now I want to build a metric that simply uses the balance before / after that transaction, but I don’t manage to do it, because I have to look across the granularity of the data.
The goal is that
Balance at Start
Balance at End
are metrics that respect the granularity chosen in the insight, so I don’t want to hard-code monthly. In the screenshot, the green columns are metrics I already have. Red is what I try to build
so, I guess I’m stuck on that one. Tried BY, EXCEPT, WITHIN, PARENT FILTER, …. A way to debug MAQL queries would be nice, but I didn’t find a good way to build the more complex queries yet
I just realized, that all I want here is lookup table functionality across dimensions. Is that maybe a thing and I was just approaching it from the wrong angle?
Hi Thomas, There is a bit of a limitation in the GD Platform when trying to force null values in dates in running tables. Unfortunately, the only work around is to have those exact dates placed in your source file. Your raw data needs to contain all the dates that include the NULL values as well. This is the only way to make the calculations work on a running date total. If you are trying to connect based on other datasets, you can define this in a metric using the “BY” clause. This can still be restricted by how your LDM has been constructed, but the basic idea of this can be found in a legacy article called, Explicit Lifting. Please note that the images are out of date, but the theory is the same in connecting datasets.
Hi Joseph, thanks for that insight. Now how would we do it on a dataset that connects to lots of different dimensions? Each filter variant would remove rows and potentially also dates, right? If we include a NULL entry for every Attribute / Value combination out there, we’d introduce billions of useless entries. Its basically not possible
But anyways, in the above example I already managed to build a metric that works with NULL dates (e.g. Last TA before slice). Now I’m stuck with actually getting that translated into the balance value, which should be a simple lookup, but across the granularity. Is there a way to deal with that?
Hi Thomas, I’m afraid that it isn’t that simply within the platform. We can raise this as product feedback, but I’m afraid that it wouldn’t necessarily be considered as working in the LDM like this could lead to terrible performance issues.