Rolling 12 Months Calculation

princye
princye Member
in Cards, Dashboards, Stories

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

image.png

Comments

  • ColemenWilson
    ColemenWilson Coach
    edited January 10

    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

    If I solved your problem, please select "yes" above

  • calvert_schaefer
    calvert_schaefer Member

    @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
    ColemenWilson Coach

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

    If I solved your problem, please select "yes" above

  • calvert_schaefer
    calvert_schaefer Member

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

    image.png
  • ColemenWilson
    ColemenWilson Coach

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

    If I solved your problem, please select "yes" above

  • princye
    princye Member

    @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
    ColemenWilson Coach

    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?

    If I solved your problem, please select "yes" above

  • princye
    princye Member
    edited April 8

    @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)

Categories