topic how to write range expression in QlikView

how to write range expression

kunkumnaveen — Thu, 27 Jul 2017 13:28:29 GMT

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

kunkumnaveen — Thu, 27 Jul 2017 13:30:18 GMT

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 — Thu, 27 Jul 2017 14:02:26 GMT

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