Need to show total for complete year for december month in a crosstab

[raghunori]

Hello Cognos SMEs, I have a requirement such that in a crosstab visualization, I need to show total for complete year in dec of previous year. For other months, it remains the monthly totals. Below is the sample scenario:

                                    Dec 2022     Jan2023      Feb2023   Mar2023    Apr2023     May2023
Opening Balance             50000           3000          5000        2000         1000            7000

where 50000= Jan2022+Feb2022+ Mar2022.............+Dec2022

I am trying this in Cognos Analytics Dashboard and not report studio

The first approach i tried is: I created an embedded calculated column running-total(Measure). And then I used a case statement like when Month=Dec then running-total(measure) else measure.

It is not giving me right results.

The second approach i tried is that I created a view of my base table. The used the filter Year=2022 in the view. But the problem with this approach is that, my views data should change whenever my base table data changes because I have filters that are pointing to my base table.

Can someone suggest an efficient way to accomplish this?

Thanks in advance

[Cheeks100]

Have you used a datasets/data modules. I don't use dashboards without them.

Datasets are essentially same as Report Stidio but instead of a report you just get a flat file that you can report off.

So you could create your 2022 query and join it to 2023 Months , create your flat file.

Pop it Data Module.  I add multiple datasets in Data modules of same subject. 

[raghunori]

Thank you. Never tried data sets. Will try.

[raghunori]

Have you used a datasets/data modules. I don't use dashboards without them.

Datasets are essentially same as Report Stidio but instead of a report you just get a flat file that you can report off.

So you could create your 2022 query and join it to 2023 Months , create your flat file.

Pop it Data Module.  I add multiple datasets in Data modules of same subject.

I have started creating datasets but do you mind elaborating on the solution. when you say 2022 query, what did you exactly mean? and how do you suggest two queries to be joined? on month end date? I am trying to use running total but i am failing miserably

[cognostechie]

Hello Cognos SMEs, I have a requirement such that in a crosstab visualization, I need to show total for complete year in dec of previous year. For other months, it remains the monthly totals. Below is the sample scenario:

                                    Dec 2022     Jan2023      Feb2023   Mar2023    Apr2023     May2023
Opening Balance             50000           3000          5000        2000         1000            7000

where 50000= Jan2022+Feb2022+ Mar2022.............+Dec2022

I am trying this in Cognos Analytics Dashboard and not report studio

The first approach i tried is: I created an embedded calculated column running-total(Measure). And then I used a case statement like when Month=Dec then running-total(measure) else measure.

It is not giving me right results.

The second approach i tried is that I created a view of my base table. The used the filter Year=2022 in the view. But the problem with this approach is that, my views data should change whenever my base table data changes because I have filters that are pointing to my base table.

Can someone suggest an efficient way to accomplish this?

Thanks in advance

Assuming your fiscal year is the same as the calendar year, create a calculated column with this expression:

Case
  When extract( year, date ) = extract( year, current_date ) -1
  Then Measure
  Else 0
End

This will give you the total from Jan till Dec of last year.

For naming this column, create another item with this:

'Dec ' + cast( extract( year, current_date ) -1, varchar(4) )

Unlock the report and use this as the column label.

[raghunori]

Thank you for your time. Appreciate it.

The problem with the formula that you gave is, I have an year filter at dashboard level that I give it for users to select. The moment they select the filter, for eg: 2023, the calculation just shows dec mon total. When I clear the year filter, the formula works and dec month shows cumulative total..

Is there a way I could make the calculation work irrespective of filter selection?

[cognostechie]

What would the users expect when they select 2023? 2023 is the current year and Dec has not come yet so there would be no data at all.

[raghunori]

Thats the million dollar question. Thanks for asking  :).. I wanted to tell this myself but I thought I would confuse you.

So the requirement is that when a user select an year, it should show all the months in that year plus december of previous year..So if they select 2023, the dashboard should show:

Dec 2022 Jan 2023 Feb 2023............July 2023

In dec 2022, they want to see the total of all the months in 2022. In other months, it should show monthly totals as usual.

So to show prev year dec when they select a year filter, in the source I created an additional record, for eg: for yr 2023, so Dec 2022 fall under 2023. This additional record is created both in fact and date dimension..

[cognostechie]

Ok, in that case, this can be done in multiple ways. You can create two queries, one for last year (depending on which year they select from prompt) and one for the year they select from prompt, set filters for one year in each query and then join both the queries to get the columns you want in the report but let's try the 2nd method , the easy method first.

In this example pYear is the name of the year prompt and [Date] is the data item which points to the date in the Fact table.

Create just one query and use this as the filter in the query:

extract (year, [Date]) >= ?pYear? -1 and extract (year, [Date]) <= ?pYear?

This will ensure that the query reads data for the year selected and it's previous year.

Create calculated columns for every month including Dec of last year. Actually, rename the Dec column to 'Last Year'  as that makes more sense.

Dec last year =   

Case
  When extract( year, [date] ) = ?pYear?  - 1
  Then Measure
  Else 0
End

Jan (of the year selected from the prompt) =

Case
  When extract( year, [date] ) = ?pYear?  and extract(month, [date]) = 1
  Then Measure
  Else 0
End

Feb (of the year selected from the prompt) =

Case
  When extract( year, [date] ) = ?pYear?  and extract(month, [date]) = 2
  Then Measure
  Else 0
End

and so on..

You might have to create a prompt which lets them specify only one year and in the data type properties of the prompt, specify 'number' so that the year they specify in the prompt becomes an integer value.

Good Luck !

[raghunori]

Thanks again!

the problem is I have multiple measures like 'Adds','Losses',Transfers' etc and each of these again for Regulars and Contractors. Which means I have to create 12*3*2, around 72 calculated measures, which I believe becomes heavy on a dashboard performance

[raghunori]

Is there a way in Cognos, one can make a filter in-effective on a calculated measure. I tried using 'total(xxx for report)' but it is not working

[cognostechie]

In that case, create two queries. One for last year and one for current year (filters in the query, not in the columns), join both of them and the third query will give you the columns you want