Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

"accumulate" distinct count with time dimensions

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

3 Replies
ferranpuchol
Partner - Contributor
Partner - Contributor

Hi everyone,

has somebody found any idea to solve this?

I'm in the same trouble and i'm not able to solve it.

Regards,

Ferran

Not applicable
Author

I think you should use set analysis like this:

sum({<Week={$('<=vWeek')}>} [Visited Customers])

where vWeek= num(Week)

Hope it helps.

Best,

T

Anonymous
Not applicable
Author

Check the following suggestions:

Calculating rolling n-period totals, averages or other aggregations

I have also attached a very quick example of AsOf that I did myself to understand the logic.

AsOf-tables should really be able to solve most of these problems.

Kind regards

Niklas