New calculated member for issues created in the last two weeks


#1

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.


#2

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


Help me! about weekly formula
#3

Thank you Lauma, this works great!


#4

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


#5

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


#6

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!


#7

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