topic Re: Cumulative count with nonexistent values in QlikView

Cumulative count with nonexistent values

Fri, 08 Aug 2014 08:25:21 GMT

Hi,

I have a chart with two dimensions :

* x-axis : the month

* another dimension who qualify the expression

I made two expressions :

* count(distinct sin.id)

* count(distinct sin.id) with the option full cumulation

When I switch in the cumulative view, I don't find the correct result. In fact, I found 86 instead au 87 because of nonexistent values for one couple of dimensions.

How did you force Qlikview to report the values of a previous month to the next month even if the value doesn't exist ?

Re: Cumulative count with nonexistent values

Anonymous — Fri, 08 Aug 2014 15:06:18 GMT

Hello Sabrina,

First I would advise you to get rid of blank values (those in amber and with - )

In the front-end:

I think the easiest would be to make a check for the existance of the measure.

If it exists, fine,

else

go bring the previous month value using Set Analysis

in the script:

Create a mapping for the complete set of values

then,

Identify those missing values (via ie len(field) > 0 check)

and then replace those missing values with the mapping table.

Courage,

Antoine

ps: if you post an application, people might be able to help you faster

Re: Cumulative count with nonexistent values

simenkg — Fri, 08 Aug 2014 15:17:47 GMT

How about using the expression

rangesum(above(aggr(Count(distinct sin.id),Month),0,rowno())

rangesum(above(aggr(Count(distinct sin.id),Month),0,rowno(Total))

Re: Cumulative count with nonexistent values

Tue, 12 Aug 2014 12:44:54 GMT

It doesn't work. I'm in a chart. I think these formulas work only in a cross table

Re: Cumulative count with nonexistent values

Tue, 12 Aug 2014 12:46:29 GMT

If I understand your explication, I replace the real dimension by a fake in case of non existent values ?