3 Replies Latest reply: Jan 21, 2015 4:22 AM by Niklas Hedborg RSS

    "accumulate" distinct count with time dimensions

    David Baumhauer

      Hi everyone,

       

      since 2 years I've been working with QlikView and always found the solution that fits my needs. Today, I ran into something I could not achieve with my knowledge, so I hope you can help me.

       

       

      In my table chart view I have two dimensions: a category and a week.

       

      What I want to see (basically 2 columns) are two things:

       

      1. already working: visited customers per week: count(DISTINCT customerID)
      2. not working: accumulated visited customers (e.g. in week 2 = visited within week 1 +2, in week 3 = visited in week 1 + 2 +3)

       

      I can not sum up the results from the previous Rows with the above function, as the result is incorrect.

       

      The result would look something like the table below (again: distinct count of customerID in week 2 is not the sum of week 1 + week 2 as there would be a double counting within the data.

       

       

       

      categoryweekvisited in weekvisited accumulated
      important11010
      important2513
      important31420
      important42025
      important51029

       

      What I came up with (which is not yet working) is the following:

       

      =aggr(rangecount(
                               above(
                               count(DISTINCT {<data_type={'call'}, Type={'A-physicians'}>} customerID),0,rowNo()
                                           )
                                 )
                 ,category,week)
      
      

       

       

      Any ideas?

       

      Regards,

      David