topic Cumulative Count DISTINCT over Months - Remove Duplicates from previous Month in QlikView

Cumulative Count DISTINCT over Months - Remove Duplicates from previous Month

Anonymous — Wed, 22 Jun 2016 14:43:00 GMT

Have a scenarios where I need to do the Cumulative Count on SalesPersons for each Month, the requirement is if the person's ID is already counted in any of the previous months it should not be Counted again e.g.:

Month ID

Jan ID01

Jan ID03

Feb ID02

Feb ID01

Mar ID02

Mar ID05

Apr ID03

Apr ID09

Apr ID01

The result should be:
Month ID_Count

Jan 2

Feb 3

Mar 4

Apr 5

Need to show it in Line chart, can't use Qlik's built in "Accumulate" option as it just adds values from previous month.

Any suggestions ?

Re: Cumulative Count DISTINCT over Months - Remove Duplicates from previous Month

swuehl — Wed, 22 Jun 2016 21:30:50 GMT

I think the approach using an AsOf table I've suggested here

Accumulated Distinct Count

should also work for you.

Re: Cumulative Count DISTINCT over Months - Remove Duplicates from previous Month

Anonymous — Thu, 23 Jun 2016 21:29:07 GMT

swuehl This works in most cases but if Jan and Mar are selected from Month filter, this shows cumulative sum of Jan, Feb and Mar against Mar.

Re: Cumulative Count DISTINCT over Months - Remove Duplicates from previous Month

swuehl — Thu, 23 Jun 2016 22:02:31 GMT

In which month field(s) are you doing the selection?