4 Replies Latest reply: Apr 13, 2016 1:58 PM by Maxime Dumas RSS

    Date functions in set analysis

    Maxime Dumas

      Hello,

       

      I am trying to filter out a dataset to keep only the last day of the current year (based on my max snapshot date selection).

       

      I am expecting something like this to work, based on what I saw on this forum:

      [Snapshot Date] = {"$(=MonthEnd(YearStart(max([Snapshot Date]))))"}

       

      If I do =MonthEnd(YearStart(max([Snapshot Date]))) in a KPI, I get the right date.

       

      Also, If I execute this function, it works:

      Max({<[Snapshot Date] = {"$(=min([Snapshot Date]))"}>}[Snapshot Date])

       

      But as soon as I add another function (like MonthEnd, AddMonths, ...), I only get "-" as a result.

       

      Any idea?

       

      Thank you!

        • Re: Date functions in set analysis
          Sunny Talwar

          Is your Snapshot Date formatted as a number? If it is, try this:

           

          [Snapshot Date] = {"$(=Num(MonthEnd(YearStart(max([Snapshot Date])))))"}

           

          or if it is date, then this may be:

           

          [Snapshot Date] = {"$(=Date(MonthEnd(YearStart(max([Snapshot Date]))), 'DateFieldFormatHere'))"}

            • Re: Date functions in set analysis
              Maxime Dumas

              [Snapshot Date] is a number when I output it in a kpi with auto format.

               

              Max({<[Snapshot Date] = {"$(=Num(MonthEnd(YearStart(max([Snapshot Date])))))"}>}[Snapshot Date]) -> Doesn't work

              Max({<[Snapshot Date] = {"$(=Num(MonthEnd(max([Snapshot Date]))))"}>}[Snapshot Date]) -> Doesn't work

              Max({<[Snapshot Date] = {"$(=Num(max([Snapshot Date])))"}>}[Snapshot Date]) -> works fine.

               

              MonthEnd should work just fine as all dates are end of month. Max(date) = '2013-12-31' and '2013-01-31' is there.

               

              As a mather of fact:

              Max({<[Snapshot Date] = {"$(=Num(min([Snapshot Date])))"}>}[Snapshot Date])   returns 41305 (2013-01-31)