Dynamic YTD calculation based on Period

[oscarca]

Hello Cognos people,

I am going to try to make this short and simple.  When working with OLAP Cubes you have the time calculations (YTD, MTD, Rolling 12 etc) as its own dimension, which is wonderful since you can slice with it in whatever ways you like.

Data Modules has come a great way with the Time intelligence feature where you can reference the built in (sample package) calendar to a specific measure, e.x. YTD[Sales].

The problem for me is that YTD will always be based on current date whereas in the Cube you can slice the YTD calulcation on whatever date you like. Is it possible to create a YTD calculation in a Data Module that works in a similar fashion. So if you create a Period prompt and choose 202008 the YTD[Sales] should be based on that Period. Right now the YTD value is always based on 202011 (Current period) and next month it will be based on 202012 no matter what Period I choose.

//Oscar

[bus_pass_man]

I really don't understand what you're getting at by "you can slice the YTD calulcation (sic) on whatever date you like" so this answer might be less helpful than you might like, but straw really is necessary if you're making bricks if the saying is true.

I think you are complaining that the relative time can't be defined and is always based on the current date.

Actually the relative time in a module is calculated based on the _as_of_date value, which will stay at that point until you change it manually.  If you have not set it, the relative time will use the current date as its reference point.  You change the value by clicking on my parameters, which is a button on the top right hand corner, if the _as_of_date parameter has been set and it has been exposed to that user. This allows individual users to set the _as_of_date for themselves, which comes with its own set of tradeoffs.

https://www.ibm.com/support/knowledgecenter/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ca_mdlg.doc/t_ca_rel_date_set_as_of_date.html
https://www.ibm.com/support/knowledgecenter/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ca_mdlg.doc/t_ca_rel_date_customize.html


I know that you need to manually set the relative time in the dynamic cube and republish it when you want to change the relative time. Transformer requires you to do something similar. I don't know of any other way to change the relative time in it.

[oscarca]

Hey Bus,

What I mean with "you can slice the YTD calulcation (sic) on whatever date you like" if you look at the image  in the hyperlink, is that the YTD member from the Time Calculation can be used with any Period of your choice inside the report. Whereas with the Data Module the YTD calculation will always be bound to the _as_of_date parameter (default current_date). I want the "as of date parameter" to change  when you change the date in the report.

https://imgshare.io/image/ytd-calculation.N0ah1y

[oscarca]

I was just curious to see and test the limiations of a Data Module and if I could recreate a crosstab as the one in the Hyperlink with a Data Module (relational tables) instead of an SSAS Cube.

[oscarca]

I am going to try to experiment to see if it is possible to use promptmacro instead of the "_as_of_date" like the code below:

Code Select Expand

// This is a template expression that is used by the column property 'Lookup reference'.
// To pass validation, the line below must remain as a comment. Do not remove the forward slashes.
// validate: 1 = 1
#$_this.parent.idForExpression# >=
        #queryValue($_this.parent.split.ref + '.dYear',
                    $_this.parent.split.ref + '.TheDate = ' + prompt ('pDate','date')#
AND
#$_this.parent.idForExpression# <= # prompt ('pDate','date') #