Oracle Analytics Cloud and Server

Products Banner

Rolling 12 Months Calculation

Received Response
395
Views
21
Comments

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.

BodyPartPctg1.PNG

Tagged:

Answers

  • 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

     

  • 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

  • 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?

  • 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

  • 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?

  • 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?

  • 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?

  • 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?

     

  • 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

  • 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.

  • scratch that thought.  its right for the first 4 recent months then its off, and there is a value for 'Back' every single month, so i don't know what the issue is.

    here is my calc after your video:  

    MSUM(ROW_COUNT, 12 BY BODY_PART_INJURED)

  • Robert, any chances you can share a DVA with a subset of your data as a source to it, so I could take a look ?

    Philippe

  • yes, thats what i was thinking, let me get that for you asap.

  • ok i am still going to get you a workbook.

    this problem is back.  when i was verifying my info, my query was wrong, not its right and it matches up from Dec 2018 going backwards to Jan 2018 is all correct but then Dec 2017 rolling 12 back to Jan 2017 is wrong since Feb 2017 has no entry, its like it skips that month and grabs from Dec 2017 and adds that number in.  i tried playing with my calc but nothing worked.

    current calc is:  MSUM(ROW_COUNT, 12 BY BODY_PART_INJURED)

    and for now i am only filtering out all others and only showing one body part injured to make this simpler.

     

     

  • I downloaded desktop, and its working now on desktop, but not on web.  I think it has something to do with refreshing, even though i am clicking refresh.  i changed it to Live instead of caching.

  • on Desktop, it is working with just one body part, by month.  so now  i added another body_part and it got all messed up.  i will send the workbook asap.

  • this is my first time sharing a workbook with OAC, so i hope i did it right.  let me know if i didn't, here it is attached. thank you for your help.

     

     

  • ok, I just edited my post and added the file to the initial post.

  • Hi Robert, I was able to get your project, but it's source is a database. unfortunately I wont be able to connect to your database. To share this project you may need to create a file based version of your example unfortunately. Possible ?

  • i replaced my workbook with one that has a csv file as a source.  try that and let me know if you have any issues seeing my workbook, thank you for your help.

    i downloaded tableau and all i had to do was click a certain button that said, Include missing values to get the months that did not exist in the data and the numbers all matched up.  does OAC have such a button?

  • Thanks Robert, I was able to download the file. Indeed your data is not dense on time (some months are missing when at this level of filtering, see barchart below.

    OAC can handle this but only by defining a time hierarchy in its metadata layer. The direct connection to the db that you currently have with DV is not yet able to handle this case (will be in future release).

    So for this to work, a 'Subject Area' needs to be created connecting to your database (either via the metadata Admintool, or the online modeling tool in OAC), within this Subject Area, time will be declared as a time hierarchy, and from this point time series function like period rolling will work with this data.

    hope that helps

    Philippe

    chart.png