running total with eazybi

[Peter Hart]

i have data in a mysql db that consists of a count of objects created in an application every week over the past few years, a date associated with the week in which the objects were created, object type, and location where it was created.  i want to create a timeline chart with a running total of each object type across all locations.  i can only configure the chart to provide a total of each object type across all locations per month.  that is, the timeline goes up and down depending how many objects were created in a given month.  i cannot get it to keep a running total.  i suspect i should be using the sum or aggregate functions in a calculated member, but i cannot seem to get that to work either.  any advice?  thanks.

[Raimonds Simanovskis]

The most effective way is to use eazyBI specific PreviousPeriods function (in JIRA Cloud or starting from the upcoming eazyBI plugin version 3.2). You can create a calculated measure with a formula

Sum(
  { PreviousPeriods([Time].CurrentHierarchyMember),
    [Time].CurrentHierarchyMember },
  [Measures].[objects created]
)

PreviousPeriods will return an optimized set of previous periods for aggregation. E.g. for Time dimension Week level member it will return a set of years from the first one until the previous year and then a set of weeks in the current year from the first week until the previous week. And then you add to this set also current Time dimension member and calculate a sum of "objects created" (replace with your own measure).

If you use eazyBI plugin before version 3.2 where PreviousPeriods function is not yet available, then you can use the formula:

Sum(
  [Time].CurrentHierarchyMember.Level.Members.Item(0):
  [Time].CurrentHierarchyMember,
  [Measures].[objects created]
)

which will calculate the sum over set of all weeks from the very first week until the current week.