13 rolling month order

[pooja]

Hi All,

This is the function lastPeriods (13,[CUBE].[Period].[BS Period].[Month] ->?SelectMonth?)  I used to populate 13 month rolling period data, which is working fine. But in output month order are coming in reverse.

Requirement-if you select March 2013 then output month order sould be-

Mar13 Feb13 Jan 13 Dec 12 Nov 12 Oct 12   Sep12 Aug12 Jul 12 Jun 12 May 12 Apr12   Mar12

But I am getting in reverse order-

Mar2012 Apr2012 May2012 Jun2012 Jul2012 Aug2012 Sep2012  Oct2012   Nov2012 Dec2012 Jan2013 Feb2013 Mar2013


Could you please help how to fix this.

Thank you.

P

[MFGF]

Hi pooja,

The months are being displayed in the order in which the month members exist within the level. This is the normal state of things. What you are looking for is to reverse this and to display the months in reverse order?

Can you advise what the MUN of a month member looks like? Find a month member in the package tree and right-click on it and select Properties. What is shown for the Member Unique Name? We need to figure out if there is a value or attribute we can use in an order() function that will allow us to get the month members in reverse order...

Cheers!

MF.

[JBCLARK20]

Hi Pooja,
I ran into a similar issue with a rolling month range. It sounds like the long way around the problem but I ended up wriiting a data item to create the list of months as YY MM and then using that to order my results. I used the months in the package in the query and in the report, but ordered the results using the created data item.

Best regards.   

[pooja]

Thank you. Not sure I am getting 100%. Could you please provide more details.

I am using dimensional package(cube). Everything works fine and just a Month order is coming reverse way.

Many Thanks.
P

[MFGF]

Thank you. Not sure I am getting 100%. Could you please provide more details.

I am using dimensional package(cube). Everything works fine and just a Month order is coming reverse way.

Many Thanks.
P

Ahhh, but you haven't answered my questions?

[pooja]

Hi MFGF,

This is what I found when right click on month member. Not sure this what you are asking-


Path  [Model].[ Balance Sheet].[BS Period].[BS Period].[Month]
Ref    [Balance Sheet].[BS Period].[BS Period].[Month]

[MFGF]

Hi Pooja,

Find a month member in your package tree, right-click on it and choose Properties.

What do you see displayed for the "Member Unique Name"?

MF.

[pooja]

Hi MFGF,

Below is the MUN

Path:[Balance Sheet].[BS Period].[BS Period].[Month]->:[TM].[BS Period].[BS Period].[@MEMBER].[Jan 2009]
Ref: [Balance Sheet].[BS Period].[BS Period].[Month]->:[TM].[BS Period].[BS Period].[@MEMBER].[Jan 2009]

MUN: [Balance Sheet].[BS Period].[BS Period].[Month]->:[TM].[BS Period].[BS Period].[@MEMBER].[Jan 2009]

[MFGF]

Ok - this means we can't sort on the MUN as it is using the short month name as part of the key to each member. Are there any other attributes in the Month level that contain month numbers?

MF.

[pooja]

Thank you MFGF,

We do not have any other attributes in the Month level that contain month numbers.


P

[MFGF]

In that case you don't have anything to sort on that will allow you to return the months in reverse order.

MF.

[pooja]

Hi MFGF,

Thank you. So if we have any other attributes in the Month level that contain month numbers then it is possible?
If so could please advise.



P

[MFGF]

Hi MFGF,

Thank you. So if we have any other attributes in the Month level that contain month numbers then it is possible?
If so could please advise.



P

Yes - if you have an attribute that contains (for example) the year and month number (eg 201404) you could use this in your order() expression.

MF.

[pooja]

Thank you MFGF,

we have modified the period hierarchy and now it is coming out as 04 2014. Could you please advise how to apply order() expression on below:

lastPeriods (13,[CUBE].[Period].[BS Period].[Month] ->?SelectMonth?)


Thanks much.

P

[MFGF]

Thank you MFGF,

we have modified the period hierarchy and now it is coming out as 04 2014. Could you please advise how to apply order() expression on below:

lastPeriods (13,[CUBE].[Period].[BS Period].[Month] ->?SelectMonth?)


Thanks much.

P

That does you no good since you are bringing back 13 months - 04 2013 will come immediately next to 04 2014 in any sort you do. You need an attribute with the year preceding the month to be able to sort the way you want to. Once you have the relevant attribute set up, use the order() function in your expression (as I indicated earlier). You can see the syntax of this if you look at the Tips for the function.

Regards,

MF.

[navissar]

In cases like this, I like to try and cheat the system. If you're displaying your months in a crosstab column, you could RTL it (Turn its direction to be right to left), effectively reversing the crosstab, and then use my buddy's Paul method here (http://www.cognoise.com/index.php/topic,11363.msg37230.html#msg37230) to move your rows to the left.

[MFGF]

In cases like this, I like to try and cheat the system. If you're displaying your months in a crosstab column, you could RTL it (Turn its direction to be right to left), effectively reversing the crosstab, and then use my buddy's Paul method here (http://www.cognoise.com/index.php/topic,11363.msg37230.html#msg37230) to move your rows to the left.

That, sir, is pure genius!

[navissar]

I appreciate the sentiment, but no, that's cheating.  :)