Year In Crosstable

[GuiCoRe]

Hello,

I have a report which contains this prompt: 201202,201203...201312.
I need to display in the table cross the month that the user had choosen (no problem): if the user choose 201301, i will show the datas for january 2013.

But I also want to diplay the month of the last year: if the user choose 201301 for example, I need to display january 2012 in the same crosstable.
My year/month data is build like this:

Year
      ->Quarter
                    ->YearMonth

So:
2012
       ->2012 Q1
                     ->201201
                     ->201202
                     ->201203
        ->...
2013
       ->2013 Q1
                     ->201301
...
My questions is: which function can i use to display the same month for the last year ? (eg to display 201201 for 201301).
(just for Information, it's just impossible to change the hierachy, datas are extract from TM1-Controller)

Many thanks for your help,

Kind regards,
GuiCoRe

[RKMI]

Hi,

Please see this, http://www-01.ibm.com/support/docview.wss?uid=swg21370852

Thanks,
RK

[GuiCoRe]

Thnak You RMI. It will help a lot !

I have another questions:
I have prompt which contains a list of member like [201201], [2001305]...

I need to show each month depending of the choice:
if the user choose 201201, i need to show 201201,201202...201212.
There is another consrtraint: for example, if the user choose 201201, 201201 should be in Actual and the other in Budget, if the user choose 201202, 201201 and 2012 should be in actual and the orther in budget...

Im' just a beginnner in multimensionnal cube report !
Thnak you for your help,

GuiCORe

[RKMI]

Hi Gui core,

I'm guessing time is on the columns. So something like this,

When user select 201201
201201, Actual 201202, Budget 201203,Budget... 201212,Budget

(Or)

When user select 201202
201201, Actual 201202, Actual 201203,Budget... 201212,Budget

So try this, put time dim prompt is in a data item and it gets the selection as one member and another data item to retrieve the set of members of the same year except for month selected and months prior. Then you can tuple the first data item with the measure (actuals) and another one with the second data item with measure(budget).

Thanks,
RK

[Rajiv]

Hi!

I'm new to cognos and while doing some practice, I did something similar to your question but in my case I wanted to see the revenue of last year's current month. What I did was, I created a data item, used it as a column in the crosstab and wrote this expression:

if([Sales (query)].[Time].[Year]=(substring(?ym?,1,4)-1) and [Sales (query)].[Time].[Month (numeric)]=substring(?ym?,5,2))
then([Sales (query)].[Sales].[Revenue])
else(0)

I hope this gives you an idea to solve your prob.

Cheers.

[GuiCoRe]

Hello, and thank you guys.

@RKMI: I can only use "201203, 201305..." as prompt because I'm using TM1 "light" Cube and I can't change it in FM.

So I used that function to get the month january: item(descendants (Ancestor([Month],2),2),0). Month is not numeric, it's a string and contains 2012,2013...
For the actuality trouble, I use this function for march: if (caption([Month])=caption([Jan]) or  caption([Month])=caption([Feb])) then ([BU]) else ([AC]): If the month I had choosen has the same name as Jan or Feb then I will use BU actuality...

I don't think that is optimize but it seems to work well :)
No Im' trying to find the past year: I choose 201203, i want 201103.

It works well with parallelPeriod for all period except for the first one:
I have 2011,2012,2013,2015 in my year hierachy. when I use parallelPeriod, I choose 201301, I have 201201, so it works. BUT when I choose 201201, I should have 201101, but it's not working.

Do you know what is the problem ? why it's not working ? If I choose 201101, i will not have anything and it's correct.

Thank you again for your help !
GuiCoRe

[RKMI]

Hi GUI Core,

I think you are rite there is probally no data in that intersection, you can verify the same by making a small validation slice. open the cube in analysis studio bring the 201101, measure and the respective line item you should see no data which will confirm what you are seeing. Or another way would be to change the parallel period defination to look 2 periods back, instead of 1 period back in which case if you select 201301 you should see 201101 data and if it ends giving you the same result the data doesn't exist.

Thanks,
RK

[GuiCoRe]

Dear RK,

You are right.  :)
I had just publish 2 periods back and now it works. It's interesting.
Now I had finished my report and I would say thank you so much for your help and don't hesitate to ask question about this !

Take care all,

Kind regards,
GuiCORe

[RKMI]

Hi GUI Core,

You are welcome, please share the code changes (before/ after) you made that lead you to the solution it might help others.

Thanks,
Rk