New calculated member for issues created in the last two weeks

[arama_mihai]

Hi,

I currently have the following setup:

Columns: Measures - Issue History and Transition Status - several statuses.

In the Rows section I added Time, and I made a filter to show issues between 2 weeks ago and today.

But what I would actually need is for the report to show only issues created between two weeks ago and today. I added a calculated member in the Time Dimension:

Aggregate(
Filter(
[Time.Weekly].[Week].Members,
DateBetween([Time.Weekly].[Week].CurrentMember.StartDate,‘2 weeks ago’,‘today’)
))

Unfortunately, it’s giving me an error when trying to expand it:

Failed to parse query, try to make query simpler.
Or maybe saved report uses deleted calculated member.
Error message:
All arguments to function ‘{}’ must have same hierarchy.

Could you please let me know what’s wrong with the formula, or if you have another way of achieving the same expected result that’s fine also.

Thank you.

[lauma.cirule]

Hi Arama!

The problem with the calculated member is that you are grouping Time dimension Weekly hierarchy members. For this, you need to specify that the calculated member is in the Weekly hierarchy

Let me know if that helped!

Please note that the Issues history measure combined with Transition status shows all issue historical statuses. If you wish to see current situation, use the Issues created measure with Status dimension.

Lauma / support@eazybi.com

[arama_mihai]

Thank you Lauma, this works great!

[hroch]

Hello, I have tried it for getting period of last 12 finished months:

Aggregate(
  Filter(
    [Time].[Month].Members,
    DateBetween([Time].CurrentHierarchyMember.StartDate,
    "13 months ago", "end of last month")    
  ) 
)

It works well, until I will try to expand some month. then the days are shown at the very end of the table (or chart), instead of within given month. Can you please advice what I did wrong?

Here is definition of my report (I did not found the way how to attach txt file):

{
  "cube_name": "Issues",
  "cube_reports": [ {
     "name": "Report for last 12 months",
     "folder_name": "Tests - Petr Nov\u00fd",
     "result_view": "table",
     "definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Issues created]"],"members":[]}]},"rows":{"dimensions":[{"name":"Time","selected_set":["[Time].[Year to date]"],"selected_set_expression":"DescendantsSet({{selected_set}}, [Time].[Month])","members":[],"bookmarked_members":[]}]},"pages":{"dimensions":[{"name":"Time","duplicate":true,"selected_set":["[Time].[All Times]","[Time].[Year to date]"],"members":[{"depth":0,"name":"All Times","full_name":"[Time].[All Times]","drillable":true,"type":"all","expanded":true,"drilled_into":false},{"depth":0,"name":"Year to date","full_name":"[Time].[Year to date]","calculated":true,"drillable":true}],"bookmarked_members":[],"current_page_members":["[Time].[Year to date]"]}]},"options":{},"view":{"current":"table","maximized":false,"table":{}}}
  } ],
  "calculated_members": [{"name":"Year to date","dimension":"Time","formula":"Aggregate(\n  Filter(\n    [Time].[Month].Members,\n    DateBetween([Time].CurrentHierarchyMember.StartDate,\n    \"13 months ago\", \"end of last month\")    \n  ) \n)","format_string":""}]
} 

Thank you

[lauma.cirule]

Hi Petr,

​Thank you for the report definition! Unfortunately, I could not repeat this locally - when expanding month I get the days still under that month. I wonder which eazyBI version are you using?

Still, unfortunately this can happen in some cases when Aggregate is used, any other hierarchy gets broken. I wonder, if Drill into might work better for you in this case?

Lauma / support@eazybi.com

[hroch]

Hello, we are using EazyBI version 4.7.0. Latest version is 4.7.1 and it things mentioned in release notes does not seems to be related.

I am actually no longer able to replicate it. Only major thing I did since the screenshot was adjustment of data Cube, but that should not be related.

Maybe it is because of Data Center which we are using. I have alredy seen replication delays and glitches for calculated members. For example I have deleted some old members and they was still available for a while, throwing an error when I tried to use or edit them.

Anyway, I no longer have the problem so I do not want to waste your time. Thank you for your help!

[lauma.cirule]

Thanks for letting me know, @hroch!
And thank you for notifying about the Data Center ‘glitches’. I will see if there is something we can do for future versions. Let us know if this becomes persistent.

Lauma / support@eazybi.com

[Monica_Diaz_Paredes]

Muchas gracias ! me fue muy útil

[nagalaxmi99]

Hi lauma - I am trying the above code to filter for 2 weeks. I am getting an ’ Do not use the other hierarchy [Time] when defining a calculated member in the hierarchy [Time.Weekly].’ Error.
What is the mistake i am doing? Can somebody help.

Aggregate(
Filter(
[Time.Weekly].[Week].Members,
DateBetween([Time].CurrentHierarchyMember.StartDate,
‘2 weeks ago’,‘today’)
)
)

[lauma.cirule]

Hi @nagalaxmi99,

When creating a calculated member in the dimension, you should use the same hierarchy (before the calculated member name) as in the formula - Time.Weekly in this case:

Additionally, we have added an MDX function DateMembersBetween, which would work faster to get the results:

Aggregate(
  [Time.Weekly].[Week].DateMembersBetween('2 weeks ago', 'today')
)

Lauma / support@eazybi.com

[jeffb]

This calculated member is not producing the correct results.

I should be seeing 8 tickets created and 6 closed

[lauma.cirule]

Hi @jeffb,

The calculated member in the Time dimension groups the time members. You can check those correct weeks are included by expanding it, e.g.

If there are some Time members missing, make sure they are added in the dimension (this usually can happen for day level with no activity in the day or if the period is in the future), see Time dimension.

Further, please check the other report context (filters you use in the pages and rows) and how measures map to the Time (Jira Core measures and dimensions). You can drill through issues by clicking on the measure and see which are included and compare with the issues you think should be included. If you find some inconsistency, please contact support@eazybi.com with more details.

Lauma / support@eazybi.com

[jeffb]

thanks that worked. i sent in a support ticket as I have a follow up issue that I am not sure how to resolve