Hello, can someone please help me debug this metric code? For some reason, it returns 0 even though today is in Q4 and there are also rows in the dataset with as_of_date from Q4 (meeting the conditions for the 4th WHEN statement):

SELECT SUM(
    CASE 
        WHEN #Q124
          {label/As_Of_Date.day} >= "2024-01-01" 
          AND {label/As_Of_Date.day} <= "2024-03-31" 
          AND THIS(DAY) >= "2024-01-01" 
          AND THIS(DAY) <= "2024-03-31"
          THEN {fact/balanace_sheet_balance},
        WHEN #Q224
          {label/As_Of_Date.day} >= "2024-04-01" 
          AND {label/As_Of_Date.day} <= "2024-06-30" 
          AND THIS(DAY) >= "2024-04-01" 
          AND THIS(DAY) <= "2024-06-30"
          THEN {fact/balanace_sheet_balance},
        WHEN #Q324
          {label/As_Of_Date.day} >= "2024-07-01" 
          AND {label/As_Of_Date.day} <= "2024-09-30" 
          AND THIS(DAY) >= "2024-07-01" 
          AND THIS(DAY) <= "2024-09-30"
          THEN {fact/balanace_sheet_balance},
        WHEN #Q424
          {label/As_Of_Date.day} >= "2024-10-01" 
          AND {label/As_Of_Date.day} <= "2024-12-31"
          AND THIS(DAY) >= "2024-10-01" 
          AND THIS(DAY) <= "2024-12-31"
          THEN {fact/balanace_sheet_balance}
        ELSE 0
    END
)

Hey Jad, thank you for reach out us 🙂 . Before continue the investigation I would like to ask what is the use case for this metric. As I can see, you may want to Sum the balance which is happening in each quarter, but more specifically ONLY in the current quarter, I am right?

Hello Mauricio, thank you for offering to help! That is correct, I'm trying to calculate ONLY the current quarter's balance_sheet_balance

Hi Jad, thank you for your answer. I was testing and I manage to have what you want, I think, with this simple metric:

SELECT sum({fact/TotalAmount}) WHERE {label/SaleDate.quarterOfYear} = THIS(QUARTEROFYEAR)

In your case must be something like:

SELECT sum({fact/balanace_sheet_balance}) WHERE {label/As_Of_Date.quarterOfYear} = THIS(QUARTEROFYEAR)

Can you test it, please?

@Mauricio Cabezas thank you for your help! The approach that our team initially followed was similar where we'd insert balance_sheet_balance in METRICS and filter it for this quarter, but we've had issues with that as GoodData's 'This Quarter' filter doesn't seem to be working right and didn't find records with as_of_date falling in the current quarter (screenshot attached). Therefore I was asked to create this metric with hard coded dates. When I apply the code you sent, I seem to be running into the same issue after I filter for this year (could be because of the use of THIS(QUARTEROFYEAR) in the metric's code)

I can see this Sum of Balance you have, come directly from the data source/table as fact type, is not a defined metric. Can you try by putting in the ROWS only the As Of Date? and also with the proposed metric I sent you first to see if you get some values. Why you did not try using a metric? In any case, you expect something like this (attached), no? In my test, TotalAmount is fact type, no attribute. And the other three columns are created metrics.

That is true. When we initially applied balance_sheet_balance, we brought it in directly since it's an existing field in a fact table in the mart. We didn't think of creating a metric since you could add that field in ROWS and select SUM, but now it seems that we do need a metric. The first screenshot is what you see when you only have as_of_date in the ROWS, and the second screenshot is what happens when you add the metric you sent (I named it qtd test) to METRICS.

> we’ve had issues with that as GoodData’s ‘This Quarter’ filter doesn’t seem to be working right and didn’t find records with as_of_date falling in the current quarter (screenshot attached). This looks suspicious because the filters shall work. There should be no reason to search for workarounds. Are you sure that there are actually data in database which match all the applied filters? Are all the date filters using the same date dataset? Is time zone configured properly in the settings? Have you tried to add quarter attribute e.g. to rows or columns to see if Q4/2024 contains any matching records? Metric with QURTEROFYEAR will sum all quarters of all the years together and not the current quarter.

SELECT sum({fact/balanace_sheet_balance}) WHERE {label/As_Of_Date.quarter} = THIS(QUARTER)

should display summary value for the current quarter the same as if the filter is applied for the metric in the UI.

My apologies for the confusion, I received an update on the requirement for this metric. This metric, since it's a QTD (Quarter To Date) measure of performance, it's supposed to calculate the difference between how the balance is doing as of today in the current quarter and the balance at the end of last quarter. I thought of constructing the metric like this but I'm not sure what I did wrong in line 7. The error started when I typed the WHERE clause, so I assume THEN doesn't take a WHERE clause?

Try to avoid using IF THEN ELSE. It will not work this way. If

IF {label/As_Of_Date.day}=THIS(Quarter)

is used in a metric which is displayed for example as headline, it will work the way that it will compute both variants THEN and ELSE and then it will have to evaluate condition for the result to chose which of the two results to use. You would need to use probably BY rules and more complex metric to get it working this way. E.g.

SELECT SUM(
  SELECT IF .... THEN SUM(...) ELSE ( SELECT SUM(...) FOR PREVIOUS(...) ) BY {label/As_Of_Date.day}
)

the by rule will tell to calculate SUM in the THEN clause for each day and also SUM for previous period by day and then based on condition for each day select which of the two computed values will be used for final aggregation using SUM. So if I understand right

SUM({fact/balance_sheet_balance})

broken down by

{label/As_Of_Date.day}

provides snapshot value of balance for each day. Maybe you can use alternative method how to compute it.

SELECT LAST_VALUE(SUM({fact/balance_sheet_balance})) 
ORDER BY {label/As_Of_Date.day} 
BY {label/As_Of_Date.quarter} 
WHERE {label/As_Of_Date.quarter} = PREVIOUS(QUARTER)

should compute last day snapshot of each quarter and filter this value for the last quarter

SELECT 
( SELECT SUM({fact/balance_sheet_balance}) WHERE {label/As_Of_Date.day}=THIS(DAY))
-
( SELECT LAST_VALUE(SUM({fact/balance_sheet_balance})) 
ORDER BY {label/As_Of_Date.day} 
BY {label/As_Of_Date.quarter} 
WHERE {label/As_Of_Date.quarter} = PREVIOUS(QUARTER)
)

should return difference between todays snapshot and last snapshot of last quarter

Thanks a lot for the helpful feedback, @Jakub Sterba! Structuring it the way you demonstrated in the last code snippet you sent makes a lot of sense. I'm not sure how to reflect the changes you suggested in the last message in MAQL. If you have a minute, do you mind jumping on a quick call? This may be more considerate from me with your time.

Try to add closing bracket after THIS(DAY)