2 Replies Latest reply: Jul 27, 2017 10:02 AM by Miguel Angel Baeyens de Arce RSS

    how to write range expression

    naveen kumar

      hello All,

      can any one suggest me what is wrong with my below expression

       

      count(DISTINCT{<SCOPE={'Yes'},VALIDFROM ={"<(max([CAL DAY])"},VALIDTO={">(max([CAL DAY])"}>}ID)


      CAL DAY has all the fiscal year  dates ,let say if Fiscal year 2017 is selected i need to take max fiscal year date from CAL DAY(31-03-2017) and based up on that valid from  has to be less than CAL DAY and valid to has to be greater than CAL DAY

        • Re: how to write range expression
          naveen kumar

          Formate

              Date(CALDAY,'DD-MM-YYYY') as "CAL DAY"

             Date(VALIDFROM,'DD-MM-YYYY') as VALIDFROM,

              Date(VALIDTO,'DD-MM-YYYY') as  VALIDTO,

           

          i am getting 0 as output when i tried

          • Re: how to write range expression
            Miguel Angel Baeyens de Arce

            At a first glance, one is syntax:

             

            Count(DISTINCT {< SCOPE = {'Yes'}, VALIDFROM = {"<$(=Date(Max([CAL DAY])))"}, VALIDTO = {">$(=Date(Max([CAL DAY])))"} >} ID)

             

            The other may come from data itself, for set analysis to work, "CAL DAY" must have the same exact format than VALIDFROM and VALIDTO, which is unlikely because the Max() function will return a numeric date, not in the form "31/03/2017". So I added the Date() function above, but you need to make sure the formats are the same