how to write range expression

[kunkumnaveen]

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

[kunkumnaveen]

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

[Miguel_Angel_Baeyens]

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