Sorting by Date Format

[bud659]

I am using a relational model with the Date is in MM:DD:YY HH:MM:SS.    I have a requirement to sort the date by a MM-YYYY and then Count all the orders in that Month.

So it should look like this

Date          Total Order Count
06-2014     100
07-2014     110

I have used to_char to display the date in the correct format,  but can't get it to sort properly.

[Lynn]

You can either change the format to YYYY-MM or else create a second data item in that format and use it as a sort key.

[bud659]

I changed to YYYY-MM and the sorting works descending..  is there a way to make it Ascending?

[bud659]

I also tried a second data item which also worked,  but it broke my total order count by Month.  The Total order count displays all the orders by each DD:MM:YY HH:MM:SS  instead of total by the MM-YYYY  (now in correct order)..   any hints?  Thank you

[Lynn]

I changed to YYYY-MM and the sorting works descending..  is there a way to make it Ascending?

Yes, change the sort direction.

http://pic.dhe.ibm.com/infocenter/cfpm/v10r1m0/index.jsp?topic=%2Fcom.ibm.swg.im.cognos.ug_cr_rptstd.10.1.0.doc%2Fug_cr_rptstd_id15543cr_rptstd_wrkdat_sort_data_rel.html

http://publib.boulder.ibm.com/infocenter/c8bi/v8r4m0/index.jsp?topic=/com.ibm.swg.im.cognos.ug_cr_rptstd.8.4.0.doc/ug_cr_rptstd_id8824cr_rptstd_wrkdat_perform_advanced_so.html

http://pic.dhe.ibm.com/infocenter/cfpm/v10r1m0/index.jsp?topic=%2Fcom.ibm.swg.im.cognos.ug_cr_rptstd.10.1.0.doc%2Fug_cr_rptstd_id18742cr_rptstd_wrkdat_sort_data_dim.html

[Lynn]

I also tried a second data item which also worked,  but it broke my total order count by Month.  The Total order count displays all the orders by each DD:MM:YY HH:MM:SS  instead of total by the MM-YYYY  (now in correct order)..   any hints?  Thank you

My car is broken. Can you fix it?

I don't really think I could expect someone to answer that question unless I was able to provide specific details.

Is your source relational or dimensional? If your report is showing ordr count down to the second then it would be 86,400 lines long for just one day's worth of activity. Is this really what you are doing? What method are you using to get the totals?

[bud659]

hi lynn.  thanks for the responses and sorry for the lack of details.  I am going to use the sorting with descending order.  this is working and thanks for your help