Parallel Period Caluclation with a prompt value (filter)

[naghamaj]

Dear All,

I am having a proplem calculating the parallel period value of a certain measure.
I have a dimension which is the branch and I have the total revenue , I should be able to calculate the parallel period value of the revenue.
The result should be :
If the user select the year 2016 in the filter , the list should show by brqnch the revenue of the year 2016 and the revenue of the year 2015 . If the user select the month October 2015 , the list should show the revenue by branch for October 2015 and October 2014.

I have attached a picture clarifying the requirement.

I count on your support sinc the project I am working on is urgent.

Thanks,
Nagham

[MDXpressor]

Hi Naghamaj,

Let me see if I am understanding...  You want the user to choose a period from a prompt, and with that period selected, show the Revenue for the selected period and it's parallel period, correct?


1st let's talk about prompting... Is the prompt meant to be optional or mandatory?
Next, lets talk about the parallel period, is it always in the same position, or will it change depending on the use of the report?  i.e. will parallelPeriod always reference 'same month last year'?  Or could it possibly be used for different periods (like a month or a quarter or a year, user's choice).

There are a couple ways to prompt a user for a Member Unique Name.  The simplest is to name the hierarchy, the point at the member:

This will provide you a tree prompt that the user can use to single select a member

Code Select Expand

[Cube].[Date Dimension].[Date Hierarchy]->?member prompt?

So, I would put that preceding code in data item and call it [Selected Month]
Then my parallelPeriod data item (named [Parallel Period]) would have the following code:

Code Select Expand

paralellPeriod([Cube].[Date Dimension].[Date Hierarchy].[Year Level], 1, [Selected Month])

Now you put [Selected Month], and [Parallel Period] into your columns and it should display the data you need.

All OLAP functions are listed in the following cognos publication, I keep mine right beside my keyboard at all times, anyone working with OLAP should.
https://www.ibm.com/developerworks/community/forums/atom/download/attachment_14246163_Dimensional_function.pdf?nodeId=8a146e34-d92c-4c38-b25f-f083bd2bf78c

Let us know how it works out!

[naghamaj]

Hi jean,

Thank you a lot for your reply.

Let me clarify some points in order to get a better understanding of the problem and to get a clear solution.

Yes I want the user to choose a period from a prompt, the value should show the selected period revenue value and its parallel period.

The prompt is mandatory.

the parallel period should change depending on the period selected by the end user. The time dimension in the prompt is a hierarchy : year - quarter - month so the values will be used in diffrent periods (month, quarter, year) so if the user choose in the filter july 2016 the parallel period value should be the revenue of july 2015 same logic for quarter and year.

the solution is working for the month selection only , what if I want to have the quarter or the year parallel periods values if slected by the end user from the hierarchy prompt?

Thanks again for yoru help and thanks for the OLAP document.

Best Regards,
Nagham 

[MDXpressor]

It's the same basic concept...

A parallelPeriod function requires a member from which to move, a level upon which to move (which must be higher than your member), and an integer to represent how many members back (at the sepecified level it should move).

So, given the code I laid out above, you should have 2 data items already: 

• [Selected Month]
• [Parallel Period]

[Parallel Period] is going to be misleading after the next 2 data items are created, so I suggest renaming it to [Prior Month].

Now, create a data item for the quarter of the [Selected Month] and we'll name it [Selected Quarter]:

Code Select Expand

parent([Selected Month])

And now you can use a parallelPeriod on [Selected Quarter] to get it's prior quarter (check my syntax here, I'm not in front of Cognos right now):

Code Select Expand

parallelPeriod([Selected Quarter], [Year], 1)

[naghamaj]

Perfect , it works very well.

thanks a lot