topic Sort date with format MM/YYYY in QlikView

Sort date with format MM/YYYY

Thu, 02 Dec 2010 11:23:37 GMT

Hello,

I have a date field built with the expression: mid(date(maydate),4,7) , the result has a format mm/yyyy. When i want to sort this field in my straight table it didn't work.

Someone can help me please.

Best Rgds.

Sort date with format MM/YYYY

googel84 — Thu, 02 Dec 2010 11:45:18 GMT

Hi Attalah Abdelhamid, use the following expression to sort your value

date#('01/' & yourDateField,'DDMMYYYY')

Let me signal you also this post to which I replied

http://community.qlik.com/forums/t/37633.aspx

Sort date with format MM/YYYY

googel84 — Thu, 02 Dec 2010 11:49:52 GMT

Sorry, I made a little mistake; the expression is

date#('01/' & yourDateField,'DD/MM/YYYY')

Sort date with format MM/YYYY

Thu, 02 Dec 2010 12:27:19 GMT

thank you googel84 for your proposition, i tried your solution but it did not work

Sort date with format MM/YYYY

googel84 — Thu, 02 Dec 2010 12:41:49 GMT

Please, provide me with more details, a qvw will be very helpful to understand your problem.

Bye

Sort date with format MM/YYYY

renjithpl — Thu, 02 Dec 2010 12:48:41 GMT

Try this...

Date(Date#(mid(date(maydate),4,7), 'M/YYYY'), 'MM/YYYY')

Regards

Sort date with format MM/YYYY

hector — Thu, 02 Dec 2010 13:21:55 GMT

Hi, you can try dual() maybe


dual(mid(date(maydate),4,7) ,maydate)

so you can sort by numeric value, because your text is going to be in MM/YYYY format, but the internal value will be a full date

rgds

PS. by the way the same result that you want can be with date(maydate,'MM/YYYY')

Sort date with format MM/YYYY

Thu, 02 Dec 2010 18:48:08 GMT

Thank u very much mr Héctor,

I have change my initial formulas by Date(mydate,'MM/YYYY') instead of mid(date(mydate),4,7), and then i just made sort by numeric value and it worked.

Thank u Tout le monde:)

Sort date with format MM/YYYY

hector — Thu, 02 Dec 2010 19:30:59 GMT

Nice.

Remember flag the topic with the selected solution, to mark it as "Solved"

Rgds

Sort date with format MM/YYYY

googel84 — Fri, 03 Dec 2010 09:51:59 GMT

Just to be clearer, the expression I suggested you was intended to be used to sort your listbox values, so you should have used it selecting the Expression option on tab Sort of List Box Properties.

Probably I hadn't been that clear.