Rolling 12 Months Calculation

ROBERT PURDY

Summary

Rolling 12 Months Calculation

Content

I need to figure out how to get a rolling 12 months on my report.  I was using MSUM function, but the numbers seem close but not quite right.  I also saw a PERIODROLLING function, i tried that, but i must not be doing something right.  Which of those two should i be using, or should i use a differernt function to figure out a ROLLING 12 Months?

my MSUM calc is:     MSUM((COUNT(*)), 11)

I highlighted in my image, the total is 695 for Nov 2018 but my database says 714.  and if i do a straight count report i get 714.

or to make it simpler for Nov 2018 most rows are correct but ARM is 32 but my database says 30.  Its almost like my months are only going back to Jan - Nov, instead of back to Dec 2017 thru Nov 2018.

i am at a loss of what to do, thanks for your help.

Philippe Lions-Oracle

Robert, it's likely that a sorting may be the issue here.

Can you please try this and confirm : run the same report, but without body-part in rows. Confirm the MSUM november value is correct in this case.

Then, filter for a single body part, and confirm that the msum is working fine as well. This is not yet the answer to your problem but a step to diagnose the pb.

let me know

tks

Philippe

 

Philippe Lions-Oracle

Also, if you are planning to use Count(*) with breakdowns analysis, I recommend you add an extra column in your dataset (prepare tab) with a dummy 1 value column, and sum it. Just prudent practice.

Philippe

ROBERT PURDY

Philippe,

i had already added a field in PREPARE named Row_Count with a 1 value column.  I was wondering if this is best practice, so thank you for saying that.

I removed all from my report and only put in Date_of_incident, then show by Month, and added the ROW_COUNT dummy field.  the number matched my database.

so then i tried my MSUM again:  MSUM(Row_Count, 11) and the numbers don't match.

ideas?

Philippe Lions-Oracle

My suspicion is that body-part in rows is what is creating the MSUM not to work.

MSUM wihtout body-part in rows should toally work, not sure you confirmed that in your note above. can you re confirm it works in this case ?

Thanks

ROBERT PURDY

i just tried what you said, and you are CORRECT, if i remove body parts, the number is correct.  could it be a null or something that is confusing OAC?

ROBERT PURDY

ok, this is interesting.

if i add in ARM alone, the number is correct.

I can add in all other body parts as long as they are AFTER the word ARM in alphabetical order.  once i add in Abdomen/Torso, the number is incorrect.

why? i must not understand this function correctly?

ROBERT PURDY

my consultant and I dug in a little deeper.  it appears that the body_parts_injured without a value in certain months is messing it all up.  if i only show body parts that have  a value EVERY month, then it works, but once i add in the body parts that have sporadic records, then the numbers don't match.  we've tried isnull, nvl, and that doesn't seem to work. any idea?

ROBERT PURDY

I was looking at another report needed, and maybe this will make it easier on the problem we are having.

i Added “ROOT_CAUSE” to my query and now There is data every month.  BUT its still not doing the MOVING SUM correctly?  But if I only look at one ROOT_CAUSE, the number matches my database.  But if I do 2 or 3 ROOT CAUSES, it gets all messed up again.  should i be using a different function for a ROLLING 12 MONTH number?

 

Philippe Lions-Oracle

Hello Robert, this 3 min video https://youtu.be/LkRHUlXvTbQ shows you a way to configure the MSUM function so that you can use it with a dimension breakdown. This is with using the BY clause in the syntax of the function.

Let me know if this is not helping with your initial issue.

Tks, Philippe

ROBERT PURDY

Philippe,

THANK YOU very much.  that helped some, but not quite all the way.  I notice if i look at my monthly counts, the most recent months info is correct until i get to Jan 2018 or Dec 2017 and its looking back a rolling 12.  I think the issue is that there isn't any value in one month so it doesn't see that data month and it jumps to the next month and grabs that info and adds it in, but then its not the right sum.

i'd love to hear more help on this or anyone's help.

while i wait, i am trying to figure out a way to write  a query to put in a body part injured and month and row count of 0 instead of 1 and then get my calculation to work.