Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
5 replies Latest Post - ‏2013-02-01T15:20:39Z by DanielWagemann
SystemAdmin
SystemAdmin
1959 Posts
ACCEPTED ANSWER

Pinned topic Average Revenue (by the number of specified weekdays in specified month)

‏2013-01-08T23:42:18Z |
Hello,

I am trying to create a crosstab with Days of the week on the X-Axis (Mon-Sun) and specified Months on the Y-axis (e.g. Jul/2012,Jul/2011,Aug/2012,Aug/2011)
The months in the Y-axis are also nested into various Products Groups.

If I use the "Revenue" measure in my cube, I will get the TOTAL revenue of all Mondays in Jul/2012.
Instead, I would like the (TOTAL revenue of all Mondays in Jul/2012) DIVIDED BY (number of Mondays in Jul/2012).

How will I be able to write this calculated measure? or would it be better to be written into the cube?
(I'm end user of Report Studio, not developer)

Thanks!
Updated on 2013-02-01T15:20:39Z at 2013-02-01T15:20:39Z by DanielWagemann
  • DanielWagemann
    DanielWagemann
    850 Posts
    ACCEPTED ANSWER

    Re: Average Revenue (by the number of specified weekdays in specified month)

    ‏2013-01-09T15:54:32Z  in response to SystemAdmin
    Out of curiosity, what happens when you drag in another query item which consists of revenue but has the aggregation set to count? Do you get the correct counts?
    • SystemAdmin
      SystemAdmin
      1959 Posts
      ACCEPTED ANSWER

      Re: Average Revenue (by the number of specified weekdays in specified month)

      ‏2013-01-31T23:02:52Z  in response to DanielWagemann
      Hi Daneil,

      Thanks for the response. Do you mean dragging a calculated measure "count(Revenue)"?? I'm not too sure how to change aggregation in Report Studio.
      But when I do count(Revenue) I just get the total revenue.

      I don't have access to framework manager, just Report Studio.
    • SystemAdmin
      SystemAdmin
      1959 Posts
      ACCEPTED ANSWER

      Re: Average Revenue (by the number of specified weekdays in specified month)

      ‏2013-01-31T23:15:15Z  in response to DanielWagemann
      I cant seem to find the "aggregate function" property in the Query Exlplorer properties window. Would the setting be on the Query or do I go into the Query and select revenue from the data items?
      • SystemAdmin
        SystemAdmin
        1959 Posts
        ACCEPTED ANSWER

        Re: Average Revenue (by the number of specified weekdays in specified month)

        ‏2013-01-31T23:19:33Z  in response to SystemAdmin
        ah found the aggregate function. Changed revenue aggregate function to count, still gives total revenue as a result.
        • DanielWagemann
          DanielWagemann
          850 Posts
          ACCEPTED ANSWER

          Re: Average Revenue (by the number of specified weekdays in specified month)

          ‏2013-02-01T15:20:39Z  in response to SystemAdmin
          Have a look a the attached 10.1.1 sample spec against the Go Sales(query) relational sample to see if the count for the days within the spec is what you are looking for.

          If it is, you could use it as the denominator for your average calc.