11 Replies Latest reply: Nov 20, 2014 6:52 AM by steve wink RSS

    Count ids with Date dimension and expressions

      Hello Community


      I´m new to QlikView and i´m facing a Problem.


      in SQL i have something like that:


      SELECT COUNT(id) FROM Table s where s.startDate <= "2014-10-31" and (s.endDate >= 2014-10-01 or s.endDate is NULL)


      In QlikView i tried something like that in expression:


      count({<startDate ={"<=$(=MonthEnd)"},endDate={"NULL",">=$(=MonthStart)"}>}id)


      Where "MonthStart" and "MonthEnd" are fields from a connected Calendar


      When i filter by a special Date i get the right value for this Month. I get all values from the Table where startDate is less then the chosen Date(MonthStart) and endDate is higher than the chosen Date(MonthEnd)


      I want to show a Chart with Dimension "Month".

      for Oct  MonthsEnd = "2014-10-31" and MonthStart = "2014-10-01"

      for Sep MonthsEnd = "2014-09-31" and MonthStart = "2014-09-01"


      But what it does right now is when i make a full accumulation, for every dimension (oct, sep, aug...) it allways uses MonthsEnd = "2014-10-31" and MonthStart = "2014-10-01".


      Please can somebody help me to achieve this problem.