1 Reply Latest reply: Jan 25, 2018 1:41 PM by Jahanzeb Hashmi RSS

    Counting number of items with date later than specified period

    Marina Dzuba

      Dear Qlik Community,


      I have requirement to calculate a number of items that were still outstanding at the end of each reporting period.


      I have a RECEIVED_DATE for each item, and a COMPLETED_DATE.  Items are placed into a month-period based on the RECEIVED_DATE (I have a table which displays RECEIVED_DATE.autoCalendar.YearMonth).


      For each RECEIVED_DATE.autoCalendar.YearMonth, I need a count of items where the COMPLETED_DATE was later than this period.  I am trying to configure a measure column with set analysis to do this count but just can't seem to get the syntax right.  Following many searches on these forums some of the syntax I've tried are:


      Count({< MasterDate = {">=$(=RECEIVED_DATE.autoCalendar.YearMonth<=$(COMPLETED_DATE.autoCalendar.YearMonth)"} >} ITEM_REF)


      count({<COMPLETED_DATE={"$(= '>=' & '01/01/2014')"}>} distinct ITEM_REF


      = count({<COMPLETED_DATE= {">$(=RECEIVED_DATE.autoCalendar.YearMonth)"}> } ITEM_REF)


      COUNT({<[COMPLETED_DATE] = {"<= [RECEIVED_DATE.autoCalendar.YearMonth]"} >}ITEM_REF)


      This may be a simple thing to achieve but I am very new and struggling.


      Please help?   Thank you!