18 Replies Latest reply: May 11, 2017 8:09 AM by Mohan Kumar RSS

    How to count distinct record with the condition match two diff. date range?

    Mohan Kumar

      Hello,

       

      I am very new to Qlik I need your help on this.

       

      I have long range of transaction data, I wanted to count the customer who is in the range of date1 and I wanted to count them if there are exist in another date range. I have a data like this,

       

      ID    Date

      7     30-12-2015

      2     1-1-2016

      3     1-1-2016

      1     2-1-2016

      4     2-1-2016

      4     3-1-2016

      1     3-1-2016

      8     4-1-2016

      9    14-1-2016

      1    15-1-2016

      2    15-1-2016

      2    17-1-2016

      1   18-1-2016


      Expression should satisfy in the following case,


      Case (Exclusive Date Range):

      Base Date Range1: 01-01-2016 to 02-01-2016

      Analysis Date Range2: 15-01-2016 to 18-01-2016

       

      From the base period range, Expression will select the ID's 1,2,3,4 then I need to count the ID's in analysis period date which matches with the Base period ID's. i.e. the matching value is 1,2. So I need the output as 2 (count of ID).

       

      I know I have to write the set analysis, but I am new to Qlik. I tried my best but still, I cannot get the expression. I have attached my sample data and .qvf with the attempt I took in this discussion. Please help and let me learn Qlik.

       

      Thanks,

      Mohan