I’m unsuccessfully for days now trying to sum $ for the the range of months prior to the date on the particular row. I know I’m stricking out because I have to tell it to look to the entire yrmth column-not just the row but I’ve tried a bunch of ways with no luck…anyone?
=Sum([$]) In ([YRMTH]) Where ([YRMTH]>=[YRMTH 13 Mth ago] and [YRMTH]<=[YRMTH] )
… It does not make sence comparing something with itself.
Did you try using previous() or relativedate() or runningsum() functions ?
Yeah I know this is where my breakdown is…my formula is only looking at the row but I want to to look at the entire column for the one dimension and I’m not sure how to do that
Sum $ where Column [YRMTH]<= row [YRMTH].
It seems like there should be an easier way to do this, but this seems to work…
=Previous([$])
+ Previous([$];2)
+ Previous([$];3)
+ Previous([$];4)
+ Previous([$];5)
+ Previous([$];6)
+ Previous([$];7)
+ Previous([$];8)
+ Previous([$];9)
+ Previous([$];10)
+ Previous([$];11)
+ Previous([$];12)
+ Previous([$];13)
In SQL Server code it would be as simple as…
SUM(YourAmount) OVER(ORDER BY YRMTH ROWS BETWEEN 13 PRECEDING AND 1 PRECEDING) AS [Rolling Previous 13 Months]
I don’t know how to translate that into a WebI formula. Could you do build your report as free-hand SQL and just to this rolling sum there?
This is a good approach but I don’t think it would work as I might have months where there is no “row” and there could be 40 rmonths with some gaps but I only want to go back 13. It would go back too far. I will mess around with it-this might get me closer than anything else but I feel like I’m missing somethin obvious
What is your database platform (e.g., SQL Server, Oracle, etc.)? Could you do free-hand SQL?
SQL-I personally cannot do free hand SQL but but I could make a request to IT but your sql above I don’t believe would work either due to their being gaps in rows…ie-month 4 and 5 don’t have a row.
I posted a suggestion for a rolling or moving sum. Check it out and vote for it…
https://influence.sap.com/sap/ino/#/idea/313668
SAP login required.
If you are allowed, you could solve this in the Universe.
Most people report out of a DWH where there is a time dimension that is normally attached to the fact table with a = join. Alias the fact table and join the time dimension to the fact with a between, this way you can create an alias for:
Maybe i am missing something but is this not the use case for RunningSum() ?
As I understand the requirement a sliding window of the previous 13 months is needed. The range for Var Sum Rolling Previous 13 Months needs to advance for each successive row.
Is there a way to accomplish that with RunningSum()?
Ah yes… I see the problem!
As per your previous post; You’re right "there must be an easier way to do this " hmmm…
Thank you -you did a great job of showing my issue. I think running sum could be the answer but I have to figure a way to “reset” on the month 13 months out. Its not as simple as just the 13 rows as there may be months with no row/data.
My idea needs some more votes to be considered. Scroll up.
This is difficult because I cannot access your data and you cannot access mine, but let’s give a free-hand SQL approach a go anyway. My examples are for SQL Server. If you have a different database platform, your IT person will need to adapt the syntax to that.
Here is my sample data. I can be found in a reproducible format at the dbfiddle link toward the end.
The central idea is that you need a data source that represents all of the time periods (i.e., YRMTH) in your range of data. We have a Calendar containing one row for every day out something like 50 years. So this query…
DECLARE @START_DATE DATETIME;
DECLARE @END_DATE DATETIME;
SET @START_DATE = '2022-01-01';
SET @END_DATE = '2023-12-31';
SELECT
x.YRMTH
, x.SomeYRMTH
, x.SomeNumber
, x.MovingSum
FROM (
SELECT
cal.YRMTH
, tmp.SomeYRMTH
, tmp.SomeNumber
, SUM (tmp.SomeNumber) OVER (ORDER BY cal.YRMTH
ROWS BETWEEN 13 PRECEDING AND 1 PRECEDING
) AS [MovingSum]
FROM (
SELECT CONVERT (CHAR(4), Y) + RIGHT('0' + CONVERT (VARCHAR(2), M), 2) AS [YRMTH]
FROM Common.dbo.Calendar
WHERE dt BETWEEN @START_DATE AND @END_DATE
AND D = 1
) cal
LEFT JOIN #SomeTable tmp ON cal.YRMTH = tmp.SomeYRMTH
) x
--WHERE x.SomeYRMTH IS NOT NULL;
Yields this…
This window function repeated below will get the sum for the previous 13 periods based on the Calendar table, not your data table. If you need to adjust the period range for which you are find the sum here is where to do it.
SUM (tmp.SomeNumber) OVER (ORDER BY cal.YRMTH ROWS BETWEEN 13 PRECEDING AND 1 PRECEDING)
When you uncomment that last line you will then just get the data from your data table having accounted for missing YRMTH time periods.
If you do not have a Calendar table, a similar result can be achieved with a common table expression (CTE). Here is a dbfiddle with the CTE code which you can run there.
Hopefully, SAP will add support for a RollingSum() function as I suggested in my idea submission linked above or someone will come up with a way to do this in Web Intelligence with the functions currently available to us without having to resort to mildly complicated free-hand SQL code. Short of either of those this may be a viable solution.
