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

    Count ids with Date dimension and expressions

    steve wink

      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.

       

      Steve