PeriodsToDate question

[cogadmin12]

All,

I have a Help Desk Ticket PowerCube with about 3 years of ticket data, with a CreateDate time dim down to the day level (yr/qtr/mon/day).  I also have a CloseDate time dim with the same levels.

I'd like to create a "backlog" report, starting with 12 months ago, showing the Open tickets at 12 months ago.  As the month changes, obviously the starting month of this report will move forward one month as well, so the calculation needs to be dynamic.

I also have a dim of Ticket Status (let's just say open or closed), and a measure of Ticket Count.   

What permutation of Time dim, Ticket Status dim, Ticket Count measure, tuples, PeriodsToDate, etc. would give me the beginning backlog (open) total?

Then for each month, the backlog will change by "Tickets opened that month" minus "Tickets closed that month".  What dimensional functions should I use to calculate a 12 month array of this calc?

Thanks for any insight.

CogAdmin12

[MFGF]

All,

I have a Help Desk Ticket PowerCube with about 3 years of ticket data, with a CreateDate time dim down to the day level (yr/qtr/mon/day).  I also have a CloseDate time dim with the same levels.

I'd like to create a "backlog" report, starting with 12 months ago, showing the Open tickets at 12 months ago.  As the month changes, obviously the starting month of this report will move forward one month as well, so the calculation needs to be dynamic.

I also have a dim of Ticket Status (let's just say open or closed), and a measure of Ticket Count.   

What permutation of Time dim, Ticket Status dim, Ticket Count measure, tuples, PeriodsToDate, etc. would give me the beginning backlog (open) total?

Then for each month, the backlog will change by "Tickets opened that month" minus "Tickets closed that month".  What dimensional functions should I use to calculate a 12 month array of this calc?

Thanks for any insight.

CogAdmin12

Is the "12 months ago" something that can be calculated based on the latest day member (category) in the cube, or does the cube have dates stretching out into the future?

To get the month members for the last 12 months (relative to the latest month in the cube), you could use an expression such as:

lastPeriods(12,closingPeriod ([your Month level]))
You could calculate the ticket count for this (for open tickets) with the expression

total(tuple([Ticket status Open member],[Ticket count measure]) within set lastPeriods(12,closingPeriod ([your Month level])))

Cheers!

MF.

[Lynn]

I know you aren't asking about cube design, but I suggest that the cube should have only one time dimension. Use your ticket status dimension and your ticket count metric to do the rest. Set up relative time for the one and only time dimension to easily do rolling months, current month, and any other dynamic periods you want.

[cogadmin12]

The cube has dates into the future.  The "Current Day" alternate drill path would contain the date of the last ticket opened.

I suppose I could look at the settings on the time dim (such as "include only when needed"), but I took the defaults.

So lastPeriods(12,closingPeriod ([your Month level])) gives me Jan thru Dec 2014.  When I actually want Sep 2013 thru Aug 2014.

I do have a "Running 13 Months" drill path created, but then you get into "using members from different hierarchies" problems if you try to use that in concert with the primary hierarchy.

CogAdmin12

[MFGF]

The cube has dates into the future.  The "Current Day" alternate drill path would contain the date of the last ticket opened.

I suppose I could look at the settings on the time dim (such as "include only when needed"), but I took the defaults.

So lastPeriods(12,closingPeriod ([your Month level])) gives me Jan thru Dec 2014.  When I actually want Sep 2013 thru Aug 2014.

I do have a "Running 13 Months" drill path created, but then you get into "using members from different hierarchies" problems if you try to use that in concert with the primary hierarchy.

CogAdmin12

In that case, can you tell us the MUN (Member Unique Name) of one of your Month members? We could use a macro to construct the MUN of the month corresponding to today but we need to know what the MUN looks like.

Cheers!

MF.

[cogadmin12]

MUN for Jan 2014:

[eFeedback_Cube].[All Create Dates].[All Create Dates].[Month]->:[PC].[@MEMBER].[20140101-20140131]

[MFGF]

MUN for Jan 2014:

[eFeedback_Cube].[All Create Dates].[All Create Dates].[Month]->:[PC].[@MEMBER].[20140101-20140131]

Thanks!

Try this:

lastPeriods(12, #'[eFeedback_Cube].[All Create Dates].[All Create Dates].[Month]->:[PC].[@MEMBER].[' + timestampMask(_first_of_month($current_timestamp),'yyyymmdd')+ '-' + timestampMask(_last_of_month($current_timestamp),'yyyymmdd') + '] '#)

This should give you a 12-month set of members up to and including the month member from your cube corresponding to today's month (ie August 2014 currently)

Cheers!

MF.