Cross tab dates format help

[crazy_tech]

Hi i am newbie in cognoise and Cognos anaytics too.. I am developing a report based on my personal finance..I have to make a two crosstab tables ..one for monthly and other yearly.. The data is coming from a fact table with accouting date is in format =2024-02 etc and their values ( personal budget)  from last financial year  2023 . the finan.month-yr is a prompt  from fact table column accounting date .so if i choose a finan.month-yr from prompt eg 2024-02 ..the first table should contain last month (jan 2024), current_month (feb 2024) and (last year current month)

____________________________________
|finan.month-yr| personal budget total|
|JAN-24          |        200.                      |
|FEB-24          |        500.                      |
|FEB-23.          |        700                        | _____________________________________


secont table should contain total last year from jan to dec( jan-dex 2024) with same date format and current year with active months (jan and feb)
____________________________________
|finan.month-yr| personal budget total|
|JAN-DEC 2023. |  5000              |                       
|JAN-FEB 2024  |  500                | 
_______________________________________
I want to have the dates in same format  and values between those dates..

Could please provide me some help..as i want to learn Cognos crazily as my name displays.. and i have only 25 days to learn as the cognos  free account expires in a month..

Thank in advance

[MFGF]

Hi,

Do you have a date dimension table joined to the fact table? Usually a date dimension will contain a large selection of date-related items, and the items you are looking to display might already be in there, making it really easy for you to add them to your crosstab?

You might also want to take a look at the relative dates capabilities. They might not be exactly what you need out of the box, but you can also customize them to your own needs:

https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=modules-relative-date-analysis

Cheers!

MF.

[crazy_tech]

I apologize for late reply.. i do have date dimension..but the prompt only accepts 'YYYY-MM' and the data should work on it.. I have checked the link you specified and try to work on it ..but the formulas and functions are not based on cognos..but  i am able to aggregate the data for annual ( second table) and monthly (first table) is already aggregated in FACT table.

[Filomena.Mignelli]

What if you create another Data Item call it YYYY (use the EXTRACT function on your finan.month-yr Data Item) and add that new YYYY Data Item to your second table instead of using finan.month-yr

[crazy_tech]

Hi i did that ..i created a data item 'dt_year_month', did to_date(finan.month-yr +'-01','yyyy-mm-dd') and did all my filters based on this data item.. the values are giving exactly i want..
Now when i chooose dt_year_month which is date and add it in cross tab as a row and try to do formatting..it is shown as string not as date as there is date option in format property.

Now in secont table since its annual data.. for example if i choose main filter as 2024-02 then in second table it need to show 'jan-feb-24' as format..

[crazy_tech]

My porblem is solved.. I changed my finan.month-yr to date and in the properties i changed the source type from legend to dataitem value and then changed the format ..now its is giving me right date