topic formatting dates in QlikView

formatting dates

Fri, 03 Aug 2012 19:19:18 GMT

Hi, I have an If statement in my Dimension Expresion box to limit my date dimension to 18 months.

=Date(If(PeriodDate > MonthStart(Today(),-18), PeriodDate, Null()), 'MMM/YYY')

It works in limiting my chart to 18 months but now the dates show as numbers ( 40600 etc ). How can I get these to show as dates. The load statements loads the dates as MM/YYYY. Thanks, Jim

Re: formatting dates

MayilVahanan — Fri, 03 Aug 2012 19:23:04 GMT

Try like this

=Date(If(PeriodDate > MonthStart(Today(),-18), PeriodDate), 'MM/YYYY')

Hope it helps

Re: formatting dates

Nicole-Smith — Fri, 03 Aug 2012 19:23:36 GMT

Try:

=If(PeriodDate > MonthStart(Today(),-18), Date(PeriodDate, 'MMM/YYY'), Null())

Re: formatting dates

Fri, 03 Aug 2012 20:29:30 GMT

still get the number. thanks for trying

Re: formatting dates

Fri, 03 Aug 2012 20:29:56 GMT

still getting the number....

Re: formatting dates

MayilVahanan — Fri, 03 Aug 2012 20:34:13 GMT

What is the PeriodDate format? is date ? or number? or text?

Try this , if its text format..Can you post a sample file

Date#(PeriodDate,'MM/YYYYY')

Re: formatting dates

Fri, 03 Aug 2012 20:41:21 GMT

It's stored in my MS SQL table as 'smalldatetime'. When I load into QV I set date format as MM/YY

Re: formatting dates

MayilVahanan — Fri, 03 Aug 2012 20:58:52 GMT

=Date(If(PeriodDate > Date(MonthStart(Today(),-18),'MM/YY'), PeriodDate), 'MM/YYYY')

What gives for this:

if(PeriodDate > MonthStart(Today(),-18), PeriodDate) number or date..if date means what format?

Re: formatting dates

muniyandi — Sat, 04 Aug 2012 06:21:07 GMT

Hi,

francis your date format as MM/YY Means (you need monthwise result ? )

Set Variable FirstDT, = Date#(DATE(MONTHSTART(TODAY(),-18),'MM/YY')))

Using Dimension expression like:

=IF (Date#(DATE(PeriodDate,'MM/YY')) >Date#(DATE(MONTHSTART(TODAY(),-18),'MM/YY')),Date#(DATE(PeriodDate,'MM/YY')),NULL())

using Variable

=IF (Date#(DATE(PeriodDate,'MM/YY')) >FirstDT,Date#(DATE(PeriodDate,'MM/YY')),NULL())

Hops it will help

Muni

Re: formatting dates

Anonymous — Sun, 05 Aug 2012 18:17:09 GMT

Looks right. See number formatting on the front end, in document properties, maybe in object (list box) properties.

Re: formatting dates

Mon, 06 Aug 2012 13:02:55 GMT

Thanks everyone for all of your help!!!