Query for filtering the tickets of last 2 weeks

Questions & Answers
1

Hello All,

I have used below query which filters the tickets which were created for last 2 weeks.
Aggregate(
Filter(
[Time.Weekly].[Week].Members,
DateBetween([Time].CurrentHierarchyMember.StartDate,
‘2 weeks ago’, ‘today’)
)

I need a similar query where in i can filter the tickets which were moved to in-progress and issue closed state for last 2 week.
Could someone please help me on it.

BR,
BG

1 Like
2

Hi,

Let me first explain a bit about your current solution. The formula works as expected because of the specific behavior of the Aggregate function when used for the Measure.

Note that the full pattern of this function for the measure is the following.

Aggregate(<Set>, <Measure>)

The function silently joins with the default measure if you do not specify explicitly the measure which should be aggregated. The default measure is “Issues created,” which in your case results in the following:

Aggregate(
Filter(
[Time.Weekly].[Week].Members,
DateBetween([Time].CurrentHierarchyMember.StartDate,
'2 weeks ago', 'today')),
[Measures].[Issues created]
)

Knowing that you can switch to a different measure in the formula to count the issues transitioned in some status during that period. The number of issues transitioned to some status (e.g. “In Progress”) can be calculated with the expression like this:

([Measures].[Transitions to status issues count],
 [Transition Status].[In Progress])

The full formula for the issues moved to the status during the last two weeks:

   Aggregate(
    Filter(
    [Time.Weekly].[Week].Members,
    DateBetween([Time].CurrentHierarchyMember.StartDate,
    '2 weeks ago', 'today')),
    ([Measures].[Transitions to status issues count],
     [Transition Status].[In Progress])
    )

There is, however, a recommendation to avoid the Aggregate function in the Measures. This function is primarily designed to aggregate members in other dimensions, not to use for the measures.

You can create an identical solution when you implement a new time dimension member with the same formula as you have currently:

image
image1514×942 104 KB

Then the above calculation looks like this:

([Measures].[Transitions to status issues count],
 [Time.Weekly].[Last two weeks],
 [Transition Status].[In Progress])

Kindly,
Janis, eazyBI support