Date manipulation

[Sep2013]

Hi i have a requirement in a report to use 2 previous months in a crosstab.
I have, in the database, a field called Fiinancial year Month, in format of '201204'.
and i want a calculation to use '201203' and '201202' from '201204'.
Please suggest what functions can be used.

[norkos]

Are you using relational or dimensional model?

If you use a dimensoinal model than you can use the lastPeriods dimensional function like this: lastPeriods(3, [Actual Month]) where [Actual Month] is a calculated member returns [201204] in this case. If you want to filter the actual month from the result set than you can achieve this with embedding the lastperiod function into a head function.

If you use relational model than you have to combine the cast, substring and _add_month cognos functions, or use the native functions of your DB provider.

[Sep2013]

I am using relational model... pelase suggest how to make that calculation

[norkos]

which DB provider do u use?

[Sep2013]

ellipse

[norkos]

I haven't got any experience with ellipse, so I don't know which DB functions to use.

I created a demo report from the "GO Sales (query)" package existings in Cognos samples. I used only cognos functions, so it should work in every enviroment.

Let you have a look, you have to create something like this.

[RubenvdLinden]

I don't know Ellipse either, but I suspect the CAST() function should work.

Dataitem One month back:

cast(if ([Financial Year Month] ends with '01') then (cast([Financial Year Month]; smallint)-89) else (cast([Financial Year Month]; smallint)-1); char(6))


Dataitem Two months back:

cast(if ([Financial Year Month] ends with '01' or [Financial Year Month] ends with '02') then (cast([Financial Year Month]; smallint)-90) else (cast([Financial Year Month]; smallint)-2); char(6))