7 Replies Latest reply on Feb 13, 2019 1:35 PM by emily nguyen

# Date Filter

Hi All,

My current data source has sales figure for every month of the year. However when I try to filter for eg. May only the only option that comes up is Jan.

Would you know why?

Thanks

• ###### 1. Re: Date Filter

Hi, Emily

create another calculation based on your [Calculation1] like below

Dateparse('dd/mm/yy',[Calculation1])

Hope this helps

ZZ

• ###### 2. Re: Date Filter

hi Emily,

Can you let me know what your "Month" calculation is? It might be that it's not being parsed correctly to convert "dd/mm/yyyy Aop" correctly to a date.

I'd expect something like

DATEPARSE('dd/mm/yyyy', LEFT([Pivot Field Name],10))

• ###### 3. Re: Date Filter

Hi Simon

Formula I was using

DATE(IF NOT ISNULL( DATEPARSE ( "MM/dd/yyyy ", left ([Pivot Field Names],find([Pivot Field Names],' ')) )  ) THEN DATEPARSE ( "MM/dd/yyyy ", left ([Pivot Field Names],find([Pivot Field Names],' ')) )   ELSEIF NOT ISNULL ( DATEPARSE ( "'F'yy ", left ([Pivot Field Names],find([Pivot Field Names],' ')) )  ) THEN DATEPARSE ( "'F'yy ", left ([Pivot Field Names],find([Pivot Field Names],' ')) )   END)

Also just tried using the formula mentioned above however still coming up with same error • ###### 4. Re: Date Filter

Hi Simon,

This is what my columns are currently looking like, tried giving your formula a go but still no luck.

My existing formula was

DATE(IF NOT ISNULL( DATEPARSE ( "MM/dd/yyyy ", left ([Pivot Field Names],find([Pivot Field Names],' ')) )  ) THEN DATEPARSE ( "MM/dd/yyyy ", left ([Pivot Field Names],find([Pivot Field Names],' ')) )   ELSEIF NOT ISNULL ( DATEPARSE ( "'F'yy ", left ([Pivot Field Names],find([Pivot Field Names],' ')) )  ) THEN DATEPARSE ( "'F'yy ", left ([Pivot Field Names],find([Pivot Field Names],' ')) )   END) • ###### 5. Re: Date Filter

Hi,

You may also try any one of these,

DATEPARSE("dd/MM/yyyy", TRIM(SPLIT([Pivot Field Name]," ",1)))

OR

DATEPARSE("d/MM/yyyy", TRIM(SPLIT([Pivot Field Name]," ",1)))

Note: In few of the data sources, DATEPARSE() function will not be available in calculated field. In that case use extract connection mode on your source and check. Else let us know we'll share another approach

Mahfooj

1 of 1 people found this helpful
• ###### 6. Re: Date Filter

So not sure what the 'F'yy' format is ...not come across that one before.

But looking a bit closer I noticed that the month doesn't have a leading zero, so I think this should do the trick

date(

dateparse('M/dd/yyyy',

left ([Pivot Field Names],find([Pivot Field Names],' '))

))

• ###### 7. Re: Date Filter

thank you!