running total with eazybi

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.

 

1 answer

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.

Suggest an answer

Log in or Sign up to answer
Community showcase
Published yesterday in Jira Ops

Jira Ops Early Access Program Update #1: Announcing our next feature and a new integration

Thanks for signing up for Jira Ops! I’m Matt Ryall, leader for the Jira Ops product team at Atlassian. Since this is a brand new product, we’ll be delivering improvements quickly and sharing updates...

205 views 0 5
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you