Rolling 12 Months Calculation

princye · 2025-01-10T07:02:06+00:00

There was an error rendering this rich post.

Trying to get the below into DOMO (below is done in PBI)

Monthly - 01/12/2024 - 31/12/2024 (1 Month) - Works for each month using the calculation - COUNT(DISTINCT Incident Number)

Rolling - 01/01/2024 - 31/12/2024 (12 Months) - Need help

FYTD - 01/04/2024 - 31/12/2024 (Apr 2024 - Dec 2024) - Need help

Find more posts tagged with

Comments

ColemenWilson

You can use the following for SSI Rolling:

(

SUM(SSI Monthly) +

LAG(SUM(SSI Monthly), 11) OVER (ORDER BY Year-Month) +

LAG(SUM(SSI Monthly), 10) OVER (ORDER BY Year-Month) +

LAG(SUM(SSI Monthly), 9) OVER (ORDER BY Year-Month) +

LAG(SUM(SSI Monthly), 8) OVER (ORDER BY Year-Month) +

LAG(SUM(SSI Monthly), 7) OVER (ORDER BY Year-Month) +

LAG(SUM(SSI Monthly), 6) OVER (ORDER BY Year-Month) +

LAG(SUM(SSI Monthly), 5) OVER (ORDER BY Year-Month) +

LAG(SUM(SSI Monthly), 4) OVER (ORDER BY Year-Month) +

LAG(SUM(SSI Monthly), 3) OVER (ORDER BY Year-Month) +

LAG(SUM(SSI Monthly), 2) OVER (ORDER BY Year-Month) +

LAG(SUM(SSI Monthly), 1) OVER (ORDER BY Year-Month))

FYTD:

(

SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END) +

LAG(SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END), 11) OVER (ORDER BY Year-Month) +

LAG(SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END), 10) OVER (ORDER BY Year-Month) +

LAG(SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END), 9) OVER (ORDER BY Year-Month) +

LAG(SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END), 8) OVER (ORDER BY Year-Month) +

LAG(SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END), 7) OVER (ORDER BY Year-Month) +

LAG(SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END), 6) OVER (ORDER BY Year-Month) +

LAG(SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END), 5) OVER (ORDER BY Year-Month) +

LAG(SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END), 4) OVER (ORDER BY Year-Month) +

LAG(SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END), 3) OVER (ORDER BY Year-Month) +

LAG(SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END), 2) OVER (ORDER BY Year-Month) +

LAG(SUM(CASE WHEN Year-Month >= '2024-04-01' THEN SSI Monthly END), 1) OVER (ORDER BY Year-Month))

Results (I don't have the rest of your data to go farther back, but the numbers are matching with what I do have:

Screen Shot 2025-01-10 at 11.28.19 AM.png

calvert_schaefer

@ColemenWilson can this be done in beast mode or is your example done via magic ETL ?

Additionally, would this methodology work if you had a transaction table with day date granularity or does your methodology require you to create a summary table truncating the day > month ?

ColemenWilson

This is using a beastmode. I built it assuming month level granularity.

calvert_schaefer

@ColemenWilson LAG() doesn't appear when I search functions in beast mode. Any thoughts ?

ColemenWilson

It will still work, it just doesn't show up in the functions list - along with many other mysql functions.

princye

@ColemenWilson - the below beast mode calculation is not giving me an error but when I insert into the table, I get an error "An issue has occurred during processing. We are unable to complete the request at this time - 'undefined' "

(
SUM(SSI Monthly) +
LAG(SUM(SSI Monthly), 11) OVER (ORDER BY Incident Date (Year-Month)) +
LAG(SUM(SSI Monthly), 10) OVER (ORDER BY Incident Date (Year-Month)) +
LAG(SUM(SSI Monthly), 9) OVER (ORDER BY Incident Date (Year-Month)) +
LAG(SUM(SSI Monthly), 8) OVER (ORDER BY Incident Date (Year-Month)) +
LAG(SUM(SSI Monthly), 7) OVER (ORDER BY Incident Date (Year-Month)) +
LAG(SUM(SSI Monthly), 6) OVER (ORDER BY Incident Date (Year-Month)) +
LAG(SUM(SSI Monthly), 5) OVER (ORDER BY Incident Date (Year-Month)) +
LAG(SUM(SSI Monthly), 4) OVER (ORDER BY Incident Date (Year-Month)) +
LAG(SUM(SSI Monthly), 3) OVER (ORDER BY Incident Date (Year-Month)) +
LAG(SUM(SSI Monthly), 2) OVER (ORDER BY Incident Date (Year-Month)) +
LAG(SUM(SSI Monthly), 1) OVER (ORDER BY Incident Date (Year-Month))
)

ColemenWilson

I think it is because you have both Incident Date and Year-Month in your ORDER BY. Also, do the fields in the beast mode match fields in your data? SSI Monthly, Incident Date, and Year-Month?

princye

@ColemenWilson -

Beastmode calculation for Incident Date (Year-Month) is DATE_FORMAT(Incident Date,'%Y %m (%b)')

Beastmode calculation for SSI Monthly is COUNT(DISTINCT Incident Number)