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

    Date Filter

    emily nguyen

      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
          Zhouyi Zhang

          Hi, Emily

           

          create another calculation based on your [Calculation1] like below

           

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

           

          Hope this helps

           

          ZZ

          • 2. Re: Date Filter
            Simon Runc

            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
              emily nguyen

              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 errorMth.PNG

              • 4. Re: Date Filter
                emily nguyen

                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)

                 

                Capture.PNG

                • 5. Re: Date Filter
                  Mahfooj Khan

                  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
                    Simon Runc

                    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
                      emily nguyen

                      thank you!