Running-Difference is failing in Crosstab report

[raghunori]

Hello team..I have a crosstab report with Month end dates (YYYY-MM-DD) in columns and measures as the rows as shown below:
   
                       2023-01-31   2022-12-31   2022-11-30   2022-10-31   2022-09-30
Opening Balance   100            200                       300                   400               250
Running-Difference             100                            100                    100        -150

But the users wanted the columns in the format Jan-2023,Dec-2022 etc. So i changed it to that format. When i did this, the running difference is failing because cognos is sorting alphabatically and hence April would come first. Even custom sorting is failing.

Do anyone know how to address the issue?

[bus_pass_man]

Define what you mean by 'Even custom sorting is failing.'   

I have my months sorted by their calendar order.  I do that by creating a sort index expression using the case function.  I include the year with the month number (so there's things like 202303, 202203, and 202103 etc. as the sort index numbers).   You can also do this in the dashboard but you would need to do this for every dashboard you create, which would be a bit of an annoyance.  It's always better to do your modelling in the modelling tool. If you can arrange it,

Just now, I created a calculation with the case function in a dashboard and was able to use it to sort another column.  The usage had to be changed to attribute for it to show up in the sort by list

You're in a bit of a pickle if you are using a FM package.

[dougp]

So, this isn't about running-difference.  It's about sorting.  Or maybe it's about formatting.

You changed the format?  It seems like you must have changed the data - Maybe you cast the date to a string data type?

Cognos will sort dates in date order.  Try using dates (not strings) and formatting them to look the way you want.

[raghunori]

I already have a sort column created at source excel sheet only. However, Cognos does not allow you to sort calculated fields by another column.. In my case, I am unable to sort 13 months rolling calculation by sort column.

[raghunori]

Define what you mean by 'Even custom sorting is failing.'   

I have my months sorted by their calendar order.  I do that by creating a sort index expression using the case function.  I include the year with the month number (so there's things like 202303, 202203, and 202103 etc. as the sort index numbers).   You can also do this in the dashboard but you would need to do this for every dashboard you create, which would be a bit of an annoyance.  It's always better to do your modelling in the modelling tool. If you can arrange it,

Just now, I created a calculation with the case function in a dashboard and was able to use it to sort another column.  The usage had to be changed to attribute for it to show up in the sort by list

You're in a bit of a pickle if you are using a FM package.

when you created a calculation, is it embedded or stand-alone?

[bus_pass_man]

I am unable to sort 13 months rolling calculation

Yeah you can't sort measures.  You can sort attributes and, in your original question, you have an attribute, which you can sort appropriately. 

So I'm not sure what the problem is.  Can you explain in slight more detail please.

[raghunori]

Imagine I have a crosstab in Cognos analytics dashboard. On columns i have a calculation (13 months rolling period) as below:

case when Date_.Month_End_Date_formatted>_add_months (maximum ( Sheet1.Month_End_Date_formatted    ),-13   ) then Date_.Month_End_Date_formatted else null end

on rows, I have all metrics like opening balance, Ending balance, Total Head Count and then Delta..Delta is nothing but running_difference(Total HC)..so far so good?

now, the problem is that the Delta metric is failing..

   31/01/22   28/02/22   31/01/23
         
opening balance   100   200   300
ending balance   400   500   700
Total HC             500  700. 1000
Delta(expected)   (no value)   200   300
Delta(now)   -300   200   50