I need help building a metric. I have two tables "Budget" and "Budget Lines". Budget has 1 row for every budget and contains the (fact) budget amount and (fact) "actual collections". Budget amount is the target budget for the budget period, "actual collections" is a field that gets populated after the budget period has ended and represents the actual collections for the budget period (for comparison purposes to see how good the budget amount was). Budget Lines has rows for every transaction related to the budget spend and contains a fact column called "budget spend". Our system has hundreds of budgets active at any one time. I am trying to develop a "Budget Spend % of Actual Collections" metric. It is reported as a percentage and in its basic sense, its just "sum(total spend) / sum(actual collections)". But the issue is that actual collections is populated retroactively. And I don't want the metric to calculate values for budgets UNITIL the actual collections field is populated. So I only want this metric to calculate on budgets that have the actual collections filled in. How do I do this?

Hi Willie, Have you considered to add there a condition, which would specify some MAX date where Actual collections ins’t equal to “null or zero” and only accept values, where the condition isn met, please?

If these are two datasets in GoodData and are connected via a reference (i.e. via budgetID), you can do something like:

SELECT SUM(total spend) WHERE IFNULL(actual collections,-1)<>-1

to get SUM of total spend of those budgets that do have actual collections filled in. Sadly I don’t think there is any IS NULL operator for facts in GoodData so you can either use this workaround with IFNULL or add some boolean attribute “collections_loaded” and filter with that one (this second option will perform faster if the transactions table is very large, since facts are usually not indexed).

You are a genius Michal. Thank you! Your solution worked.