"accumulate" distinct count with time dimensions

[Anonymous]

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.

category	week	visited in week	visited accumulated
important	1	10	10
important	2	5	13
important	3	14	20
important	4	20	25
important	5	10	29

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

[ferranpuchol]

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

[#3]

I think you should use set analysis like this:

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

where vWeek= num(Week)

Hope it helps.

Best,

T

[Anonymous]

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