There is a blog about running QTD and YTD that is posted by @Sefan_Linders
https://community.sap.com/t5/technology-blogs-by-sap/running-year-to-date-ytd-on-fiscal-periods-with...
My customer wants to implement this on top of flipping sign functionality that I posted earlier.
https://community.sap.com/t5/technology-blogs-by-sap/sap-datasphere-how-to-flip-sign-the-measure-val...
To achieve this, I tried to implement the solution and used a Large Language Model (LLM) for optimizing the SQL statement. The LLM suggested that using window functions is more efficient than using subqueries or self-joins, as it improves readability and allows calculations without altering the query structure.
Here is the optimized SQL query provided by the LLM:
SELECT ACCT, Date, Version, CostCenter, Amount,
SUM(Amount) OVER (PARTITION BY ACCT, CostCenter ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS YTD,
SUM(Amount) OVER (PARTITION BY ACCT, CostCenter, YEAR(Date), QUARTER(Date) ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS QTD
FROM Fact
ORDER BY ACCT, CostCenter, Date, Version.
I hope this helps someone who wants to implement QTD and YTD in SAP Datasphere.
Bluesky
