topic Accumulated distinct count and sum in one chart in QlikView

Accumulated distinct count and sum in one chart

Anonymous — Tue, 10 Apr 2018 14:52:42 GMT

Hello,

I try to create a chart with a mix of cumulated values.

On one hand I have to sum the cumulated calls per month for specific customer groups (this is already working),

further I have to count the cumulated unique customers with calls for the selected period. --> Like the example:

The problem is, that the count of the cumulated customers per Month is not working as whished.

I tried this by an expression like:

rangesum(before(TOTAL count(DISTINCT {1} { < MyEpression= {'abc'} > } %ID),0,ColumnNo(TOTAL)))

But the expression is cumulating the unique customers per month and adding this to the previous month value.

Due to this the Customers are not unique over the different month.

For instance,

in February I want to know the number of unique customers for January and February

in March I want to know the number of unique customers for January, February and March...

Many thanks and best regards,

Claus

pascos88 — Tue, 10 Apr 2018 15:03:55 GMT

For the cumulated operation try this:

Rangesum(above(count({ < MyEpression= {'abc'} > } >} 'Your_Field'),0,rowno()))

Hoping this helps.

Anonymous — Tue, 10 Apr 2018 15:24:05 GMT

Many thanks for your response!

I guess this is depending from the table design.

In my case I want to cumulate by month and not by row.

Due to this I think "rangesum(before(TOTAL count(DISTINCT {1} { < MyEpression= {'abc'} > } %ID),0,ColumnNo(TOTAL))) " should be ok?

Best regards,

Claus

sunny_talwar — Tue, 10 Apr 2018 15:28:35 GMT

The best way to address this problem is to use The As-Of Table

Anonymous — Wed, 11 Apr 2018 12:55:53 GMT

Hello Sunny,

Many thanks for your answer!

I need some time to understand the idea behind the "as of table" but I think I get it now.

After implementation of the solution the calculation is now correct.

Best regards,

Claus