topic Month Year Field in QlikView

Month Year Field

Mon, 30 Jan 2012 10:02:27 GMT

Hi,

i have a date field in the format of dd/mm/yyyy.

i want to create another field which uses the date field to get it in the format of mm/yyyy, so i can use it as a dimension on a graph.

What is the qlikview code to do this?

Thanks

Laura

Month Year Field

perumal_41 — Mon, 30 Jan 2012 10:08:01 GMT

Hi Laura,

Use this Script Syndex Date(Field1,'MMM/YYYY').

Regards,

Perumal.A

Month Year Field

Anonymous — Mon, 30 Jan 2012 10:12:03 GMT

Create a field like below in your table.

Month(Date_Field) & '/' & Year(Date_Field) AS MonthYear

so that you can use the MonthYear field in your graphs as dimensions.

Thanks,

Leni

Month Year Field

Mon, 30 Jan 2012 10:12:18 GMT

Hi , This worked to some extent but it has created several of the same dates.

It hasnt grouped the data in the same month and year.

Any advice?

Thanks

Laura

Month Year Field

perumal_41 — Mon, 30 Jan 2012 10:16:13 GMT

Hi Laura,

Try this script Date(makedate(year(today()),month(today()),Day(today())),'MMM/YYYY')

Regards ,

Perumal A

Month Year Field

orital81 — Mon, 30 Jan 2012 10:16:17 GMT

Try using This:

Date(YourDateField,'MM/YYYY').

Month Year Field

Mon, 30 Jan 2012 10:36:09 GMT

The new problem is that it will not sort them in Date Order

i have tried all the combinations of the sort properties but none of them work.

Any ideas?

Month Year Field

Anonymous — Mon, 30 Jan 2012 11:25:54 GMT

Hi,

The field Month(Date_Field) & '/' & Year(Date_Field) AS MonthYear is a string because we are concatenating two fields. So it will sort in an alphabetical order.

If you want to sort them in Date order then use the below one:

Date(Monthstart(Date_field), 'MMM/YYYY') as MonthYear

Thanks,

Leni