Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

kunkumnaveen
Contributor III

how to write range expression

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

2 Replies
kunkumnaveen
Contributor III

Re: how to write range expression

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

MVP
MVP

Re: how to write range expression

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

Community Browser