Wednesday, March 14, 2012

Create Chart with Rolling Totals Data

Business Case:
User wants to create a chart report that displays rolling 12 months revenue by for current year (In this example 2010). Although 2009 revenues should be used in calculating the rolling totals, the chart should display only 2010 months.

Implementation:
  • Create a base query with data for Prior Year (2009) and and Current Year (2010). My data sample looks like this:

  • Set the aggregate property of Revenue in the Base Query to "Total".
  • Add a data item called [Dummy] with expression 'Dummy'.
  • Create a new query, call it CY and drag the base query as a short cut
  • Add the data items from the base query to CY query.
  • Add a filter in 'CY' query to filter only for current year's data. In this example, I added:
extract(year, [Order Date]) = 2010
  • Create a new query called summary and add a join element.
  • Add Base query and CY to the join element. The queries should look like this:

  • Join the queries based on the data item [Dummy]. We are basically creating a cartesian product, but since cognos needs a join, we are using a dummy column.
  •  Now that we have created a cartesian product, we should limit the data to rolling 12 months. So add this filter to the Summary query:
[Base Data].[Order Date] between _add_months([CY].[Order Date],-11) and [CY].[Order Date]
  • Add the [Date] from 'CY' query and revenue from "Base Data" Query.
  • Create the chart based of the "Summary" query.  The report output will look like this:
  •  If you compare the tabular data and the base data above, you will see that each month's revenue includes that month and the previous 11 months.

11 comments:

  1. Replies


    1. Hàn Giang Đào tiếp tục nói:

      - Lúc trước, Nhạc gia liên hợp tứ đại tộc nhân và tứ đại thú tộc đã đánh cho HắcÁm thần điện trọng thương, nhưng về sau xảy ra một chuyện khiến cho Nhạc gia bị chia năm xẻ bảy, tứ đại thú tộc và tứ đđồng tâm
      game mu
      cho thuê phòng trọ
      cho thuê phòng trọ
      nhac san cuc manh
      tư vấn pháp luật qua điện thoại
      văn phòng luật
      số điện thoại tư vấn luật
      dịch vụ thành lập doanh nghiệp
      http://we-cooking.com/
      chém gióại tộc nhân cũng ngừng hợp tác, sau đó tứ đại thú tộc và tứ đại tộc nhân biến mất khỏi đại lục.

      - Viện trưởng, người nói bọn họ sẽ không bỏ qua cho tại hạ là vì sao?

      Nhạc Thành cất tiếng hỏi.

      - Bởi vì ngươi có thân thể Hoàng Long, huyết mạch đấu đế, đây là uy hiếp rất lớn đối với bọn họ, nhất định sẽ không bỏ qua cho ngươi.

      Hàn Giang Đào nói.

      - Viện trưởng, tứ đại nhân tộc, tứ đại thú tộc là gì?

      Nhạc Thành tiếp tục hỏi, trong lòng hắn thầm suy nghĩ, Hắc Ám Thần điện nếu thật sự đến gây chuyện thì mình cũng không thể bỏ qua cho bọn họ.

      Delete
  2. Sriram,could it be possibe to post report XML,please.Thanks

    ReplyDelete
  3. Hi Sriram,

    This is a totally different question. I have a prompt YrQtr which has data like 2009Q1 etc. I need the YrQtr limit to set default to current yearqtr+rolling 3 yrqtrs eg: 2013 Q3, 2013Q4, 2014Q1, 2014Q2. When I move to the next qtr it shuld automatically set to 2013Q4, 2014Q1,2014Q2,2014Q3 and so on. Any idea on how I can achieve this in report studio?

    Thanks!

    ReplyDelete
    Replies
    1. How are you currently populating the value prompt. This should be query driven. You should have a date dimension for this to work properly and use appropriate filter in the prompt query to get the desired set of values.

      Delete
  4. Sriram , this is entirely different question,

    I am looking for your immediate attention , not sure this question is silly.


    We got around 48 rows for single date as values are captured for every 30 mins .


    The requirement is the graph should display as attached , however the timestamp values has to be hidden and only it should report "DATE" alone in X axis.


    I achieved till splitting it to "DATE" and "TIME" query items , however I needs to place it in chart to show the graph in the attached fashion . How can I make the graph undisturbed without showing time dimension .


    Thanks,

    Sasikumar.

    ReplyDelete
    Replies
    1. Sasikumar, There was no graph attached. Can you email me the expected output?

      Delete
  5. As we all know, that hard disk is one of the data storage tape options that are reducing in cost very quickly, but that does not mean that they are increasing in the ability to be more solid storage options. Self Storage

    ReplyDelete
  6. This step condenses the business model into an abstract representation on paper, so it can be easily seen and understood by the business managers on one hand, who can improve the process prior to implementation;seo dashboard

    ReplyDelete
  7. Of course environment is also important, and while genetics vs. environment is debatable in mental development, physical potential is largely genetic. Depending on your parents, there is a limit as to how strong you are going to be.visit site

    ReplyDelete