Set a start for cumulative data

I have a calculated member as follows:
Cache(
sum({PreviousPeriods([Time].CurrentHierarchyMember),
[Time].CurrentHierarchyMember},
[Measures].[Issues closed])
)
When I use this, the Cumulative data starts accumulating at the dawn of time. I would like to report this on a specific period, but I can not figure out the correct filter to only use data for the last 3 months. In other words, I i filter Time to only show the last 3 months, the value starts at 6000 in the first period shown when I want to start at 0.

I am not sure if I need to do something on the measure or if I can create a custom time member to only get data for the last three months.

Hi,

Assuming that you use dynamic date filtering, you could try creating a new calculated measure that would calculate the cumulative results only for the last 3 months (see picture below)
Try this code:

CASE WHEN
DateAfterPeriodEnd(
[Time].CurrentHierarchyMember.Nextmember.startdate,
[Time].[Month].CurrentDateMember.Lag(2) --returns the month - 3 months ago
)
AND
DateBeforePeriodEnd(
  [Time].CurrentHierarchyMember.StartDate,
  [Time].[Month].CurrentDateMember
)
THEN
Cache(
sum({

[Time].[Month].CurrentDateMember.Lag(2): --returns the month 3 months ago
[Time].[Month].CurrentHierarchyMember
},
[Measures].[Issues created])
)
END

See also more about LAG function here:
https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-members#Calculatedmembers-Movingintime

Actually, quite soon we are about to release a newer version of eazyBI where such calculations would be much easier. It is already being tested but I can’t share ETA at the moment.

Martins / eazyBI support

This did not work when I tired to do a daily trend line for the three months. However I am very close.

I have created a calculated member for time:
Aggregate(
{Filter([Time].[Day].Members,
DateBetween([Time].CurrentMember.StartDate,‘3 months ago’,‘End of last month’))
})

I am then using this to calculate the cumulative measure:
CASE WHEN
DateAfterPeriodEnd(
[Time].CurrentHierarchyMember.Nextmember.startdate,
[Time].[Day].CurrentDateMember.Lag(
DateDiffDays(‘3 months ago’,‘today’)
)–returns the month - 3 months ago
)
AND
DateBeforePeriodEnd(
[Time].CurrentHierarchyMember.StartDate,
[Time].[Day].CurrentDateMember
)
THEN
Cache(
sum(
{ChildrenSet([Time].[Prior 3 Full Month Days]).Item(0):
[Time].CurrentMember},
[Measures].[Issues closed]
)
)
ELSE
0
END

The issue I have now is that I want the report to dynamically figure out what the last 3 fulls months are. I need to figure out ‘First Day of the Month 3 Months Ago’ in chronic lingo. I have tried many different ways, but can not get the time periods to start on December 1 (Today is Mar 28)

Any advice?

Hi,

In case of a daily trend, you could do the following

  1. Create a calculated member in “Time” dimension with a name “Days from last 3 full months”

    Aggregate(
    Filter(
    [Time].[Day].Members,
    DateBetween([Time].CurrentMember.StartDate,
    DateSerial(Year(Now()),Month(Now())-3,1),
    DateSerial(Year(Now()),Month(Now()),0)
    )
    )
    )

  2. Then create a cumulative measure

    CASE WHEN
    DateInPeriod(
    [Time].CurrentHierarchyMember.startdate,
    [Time].[Days from last 3 full months]
    )
    THEN
    sum({
    ChildrenSet([Time].[Days from last 3 full months]).item(0):
    [Time].CurrentHierarchyMember
    },
    [Measures].[Issues closed]
    )
    END

But this calculation would not help for the Monthly trend.

Martins / eazyBI support

1 Like

Hi
This was very helpful
How do i add CoalesceEmpty to this formula in order that the 0 shows up on the graphs

@IGMF

Try this formula:

CoalesceEmpty(
CASE WHEN
DateInPeriod(
[Time].CurrentHierarchyMember.startdate,
[Time].[Days from last 3 full months]
)
THEN
sum({
ChildrenSet([Time].[Days from last 3 full months]).item(0):
[Time].CurrentHierarchyMember
},
[Measures].[Issues closed]
)
END
,0)

Martins / eazyBI

Hi
Thank you
I was putting the 0 in the wrong place
I put it after the ) before end
Now it works

Thank you

1 Like

Hello Martins,
As mentioned by you I have created below two members

  1. a calculated member in “Time” dimension with a name “Days from last 3 full months”
  2. Then create a cumulative measure in measure diamention but getting the below error.

Can you please look into this?

Thanks & Regards,
Vrushali Hasbe.

Hello @Vrushali_Hasbe

What formula did you use to create the “Days from last 3 full months” member in “Time” dimension?

Martins / eazyBI

Hello Martins,

Below formula I am using create the “Days from last 3 full months” member in “Time” dimension.

Aggregate(
Filter(
[Time].[Day].Members,
DateBetween([Time].CurrentMember.StartDate,
DateSerial(Year(Now()),Month(Now())-3,1),
DateSerial(Year(Now()),Month(Now()),0)
)
)
)

Let me know if you want any other information from my side.

Thanks & Regards,
Vrushali Hasbe

How exactly did you get the screenshot with month-level if your calculated member “Days from last 3 full months” show day level when expanded? When I create exactly the same member in “Time” dimension and expand it, I get day level members. For your calculate member the calculated measure code would be different.

Please export and share the definition of your report

Martins / eazyBI

Hello Martins,

Let me explain my entire requirement to you.

I have my original report below(Based on Issues created).
image

So by taking the reference of above report I need to create a new report based on the Cumulative count of issues created. and that report exactly looks like below.
image

and cumulative sum needs to start from April Month.

I hope my requirement is now clear to you.

Also I have attached my Original report definition with you. please check that and guide me accordingly.

{
  "cube_name": "Issues",
  "cube_reports": [ {
     "name": "Defect Escape Ratio sample",
     "folder_name": "Exec Reports",
     "result_view": "table",
     "definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Issues created]"],"members":[]},{"name":"Time","selected_set":["[Time].[Last 6 Months]"],"selected_set_expression":"DescendantsSet({{selected_set}}, [Time].[Month])","members":[{"depth":3,"name":"Jan 2022","full_name":"[Time].[2022].[Q1 2022].[Jan 2022]","drillable":true,"removed":true},{"depth":3,"name":"Feb 2022","full_name":"[Time].[2022].[Q1 2022].[Feb 2022]","drillable":true,"removed":true},{"depth":3,"name":"Mar 2022","full_name":"[Time].[2022].[Q1 2022].[Mar 2022]","drillable":true,"removed":true}],"bookmarked_members":[]}]},"rows":{"dimensions":[{"name":"Issue Type","selected_set":["[Issue Type].[QE/GT Defect]","[Issue Type].[Defect Escape Ratio]","[Issue Type].[External Defect]"],"members":[],"bookmarked_members":[{"depth":1,"name":"External Defect","full_name":"[Issue Type].[External Defect]"}]}],"order_by":{"expression":["[Measures].[Issues created]","[Time].[2022].[Q1 2022].[Feb 2022]"],"order":"ASC"}},"pages":{"dimensions":[{"name":"Time","duplicate":true,"selected_set":["[Time].[Last 6 Months]"],"members":[{"depth":0,"name":"Last 6 Months","full_name":"[Time].[Last 6 Months]","calculated":true,"drillable":true,"dimension":"Time"}],"bookmarked_members":[],"current_page_members":["[Time].[Last 6 Months]"]},{"name":"Project","selected_set":["[Project].[Project Group]"],"members":[{"depth":0,"name":"Project Group","full_name":"[Project].[Project Group]","calculated":true,"drillable":true,"dimension":"Project","formula":"Aggregate(\n  {\n  [Project].[Roaming Analytics ],\n  [Project].[Roaming-Policy ],\n  [Project].[Steering and other signaling ],\n  [Project].[Service Enablers And RoamFlow],\n  [Project].[WBA ],\n  [Project].[Infra and DevSecOps ],\n  [Project].[SDS Platform],\n  [Project].[Others]\n  }\n)","format_string":"","annotations":null,"dependent_calculated_members_count":0,"dependent_report_ids":[1053246,1077481,1072908,1060635,1069453,1077736,1074921,1090053,1074321,1068506,1074928,1077737,1074885,1076454,1074194,1074193,1074195,1068281,1074235,1072920],"expanded":true,"drilled_into":false,"id":"[Project].[Project Group]"}],"bookmarked_members":[],"current_page_members":["[Project].[Project Group]"]},{"name":"Status","selected_set":["[Status].[Non-Rejected]"],"members":[{"depth":0,"name":"Non-Rejected","full_name":"[Status].[Non-Rejected]","calculated":true,"drillable":true,"dimension":"Status"}],"bookmarked_members":[],"current_page_members":["[Status].[Non-Rejected]"]},{"name":"Defect Root Cause","selected_set":["[Defect Root Cause].[Except Enhancements]"],"members":[{"depth":0,"dimension":"Defect Root Cause","name":"Except Enhancements","formula":"Aggregate(\n  Except( [Defect Root Cause].[Defect Root Cause].Members,\n    {\n    [Defect Root Cause].[Enhancement],\n    [Defect Root Cause].[GUI usability enhancement]\n    }\n  )\n)","format_string":"","full_name":"[Defect Root Cause].[Except Enhancements]","drillable":true,"annotations":null,"calculated":true}],"bookmarked_members":[],"current_page_members":["[Defect Root Cause].[Except Enhancements]"]}]},"options":{},"view":{"current":"table","maximized":false,"table":{"row_dimension_headers":{"Issue Type":"Defect Type"},"freeze_header":false}},"calculated_members":[],"description":"\u003cb\u003eEscape Ratio = External Defect / (QE Defect + General Testing Defect). \u003c/b\u003e\nExcludes the defects that have been ‘Rejected’ / 'Rejected by Dev', or where Defect Root Cause has been marked as 'Enhancements' in Defect."}
  } ],
  "calculated_members": [{"name":"Last 6 Months","dimension":"Time","formula":"Aggregate(\n  [Time].[Month].DateMembersBetween('6 months ago', 'today')\n)","format_string":""},{"name":"Defect Escape Ratio","dimension":"Issue Type","formula":"[Issue Type].[External Defect] /\n([Issue Type].[QE Defect] + [Issue Type].[General Testing Defect])","format_string":"#0%"},{"name":"QE/GT Defect","dimension":"Issue Type","formula":"Aggregate\n( {\n[Issue Type].[QE Defect],[Issue Type].[General Testing Defect]\n\n} )","format_string":""},{"name":"Non-Rejected","dimension":"Status","formula":"-- annotations.disable_drill_into=true\n\nAggregate(\n  Except(\n    [Status].[Status].Members,\n    {\n    [Status].[Rejected],\n    [Status].[Rejected by Dev]}\n  )\n)","format_string":""},{"name":"Roaming Analytics ","dimension":"Project","formula":"Aggregate(\n  {[Project].[Pack Recommendations 1.0],\n  [Project].[Profitability Analytics 1.0],\n  [Project].[Profitability Analytics 2.1],\n  [Project].[Roamer Analytics 3.4],\n  [Project].[Rule Builder],\n  [Project].[IoT Packet Printing],\n  [Project].[Wisdom-Core],\n  [Project].[RCEM 2.2],\n  [Project].[RCEM 2.3],\n  [Project].[RCEM 3.0],\n  [Project].[RCEM 3.1],\n  [Project].[Interconnect Analytics]}\n)","format_string":""},{"name":"Infra and DevSecOps ","dimension":"Project","formula":"Aggregate(\n  {\n  [Project].[R\u0026D Systems],\n  [Project].[DB CoE]\n  }\n)","format_string":""},{"name":"Roaming-Policy ","dimension":"Project","formula":"Aggregate(\n  {\n  [Project].[Short Codes 7.0],\n  [Project].[Smart Call Assistant 7.0],\n  [Project].[VMCC 7.0],\n  [Project].[Voice Policy Engine 1.0],\n  [Project].[Voice Policy Engine 2.0],\n  [Project].[RoamPolicy 3.0],\n  [Project].[RoamWallet 3.0],\n  [Project].[LSC]\n  }\n)","format_string":""},{"name":"SDS Platform","dimension":"Project","formula":"Aggregate(\n  {\n  [Project].[Cos Engine Adaptor],\n  [Project].[SDS],\n  [Project].[PCEF]\n  }\n)","format_string":""},{"name":"Service Enablers And RoamFlow","dimension":"Project","formula":"Aggregate(\n  {\n  [Project].[AMMS 1.0],\n  [Project].[CDMAHLR Sync 1.0],\n  [Project].[GTPProxy 7.0],\n  [Project].[MISM 6.0],\n  [Project].[Multiline 1.0],\n  [Project].[Multiline 1.0],\n  [Project].[RoamFlow],\n  [Project].[Roaming Replicator 7.0],\n  [Project].[Signaling Packet Relay System 7.0],\n  [Project].[SIMM 6.0],\n  [Project].[Smart Mediator 2.0],\n  [Project].[EIR 7.0]\n  \n  }\n)","format_string":""},{"name":"Steering and other signaling ","dimension":"Project","formula":"Aggregate(\n  {\n  [Project].[Missed Call Alert],\n  [Project].[RoamFilter],\n  [Project].[RoamProtect/IRTMS],\n  [Project].[Seamless Capture],\n  [Project].[SMS Relay],\n  [Project].[Subscriber Location Tracker],\n  [Project].[Border Roaming Gateway 3.0],\n  [Project].[Steering 7.0],\n  [Project].[Steering 8.0],\n  [Project].[Steering 8.2],\n  [Project].[Steering 9.0],\n  [Project].[Steering Analytics 1.0],\n  [Project].[Traffic Steering Intelligence 3.0],\n  [Project].[iCampaign 1.0],\n  [Project].[Outreach Messaging],\n  [Project].[Outreach Messaging],\n  [Project].[GDPR Adapter],\n  [Project].[GLR 3.0],\n  [Project].[GLR 3.1]\n  }\n)","format_string":""},{"name":"WBA ","dimension":"Project","formula":"Aggregate(\n  {\n  [Project].[RBS],\n  [Project].[Wholesale Business Advisor],\n  [Project].[Blockchain]\n  }\n)","format_string":""},{"name":"Others","dimension":"Project","formula":"Aggregate(\n  {\n  [Project].[Roaming Penetration Testing],\n  [Project].[UI Engineering],\n  [Project].[EIR 7.0],\n  [Project].[Re-Routing Solutions 1.0],\n  [Project].[5G CHF 1.0],\n  [Project].[5G PCF 1.0],\n  [Project].[5G SCP],\n  [Project].[HSS],\n  [Project].[GSC],\n  [Project].[PGW]\n  \n  }\n)","format_string":""},{"name":"Project Group","dimension":"Project","formula":"Aggregate(\n  {\n  [Project].[Roaming Analytics ],\n  [Project].[Roaming-Policy ],\n  [Project].[Steering and other signaling ],\n  [Project].[WBA ],\n  [Project].[SDS Platform],\n  [Project].[Others],\n  [Project].[Service Enablers And RoamFlow]\n \n  }\n)","format_string":""},{"name":"Except Enhancements","dimension":"Defect Root Cause","formula":"Aggregate(\n  Except( [Defect Root Cause].[Defect Root Cause].Members,\n    {\n    [Defect Root Cause].[Enhancement],\n    [Defect Root Cause].[GUI usability enhancement]\n    }\n  )\n)","format_string":""}]
}

Thanks & Regards,
Vrushali Hasbe

Try using “Standart calculations” > cumulative sum for the “Issues created” measure and then unselect “Issues created” measure.

https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-Addstandardcalculationsbasedonaselectedmeasure

Martins / eazyBI

Actually, we would recommend creating ratio as new calcualted measure with % integer format and organize your report in vertical position (then cumulative sum should work as expected from my previous post)

Hi Martins,

In the below report I have used Standart calculations” > cumulative sum and my report is looks like as below.
image

I am calculating Defect Escape ratio as :

Defect Escape Ratio= (Cumulative Ext Defect) / (Cumulative GT Defect)

May I get the measure defination from you for the calculation of same?

In addition please let me know what formula you have used for the calculation of Story ratio.

Thanks & Regards,
Vrushali Hasbe

@Vrushali_Hasbe

Try creating a new calculated measure (with integer percentage format) using the simple mathematical expressions using tuples.

(
  [Measures].[Issues created],
  [Issue Type].[Story]
)
/
Aggregate(
  {
    [Issue Type].[Story],
    [Issue type].[Bug],
    [Issue type].[Defect]
  },
  [Measures].[Issues created]
)

Martins / eazyBI