topic count until value changes (in diagram) in QlikView

count until value changes (in diagram)

Tue, 05 Jan 2016 08:11:43 GMT

hello and a happy new year to everyone

i've got a question about a counting function:

how can i count the rows until the value in one column changes.

ex:

i want to count all "A's" before the value changes to "B"

thx for your help!

Re: count until value changes (in diagram)

sunny_talwar — Tue, 05 Jan 2016 14:42:56 GMT

Where are you wanting to count this? Script or front end? And you only want to count As? and not Bs

Re: count until value changes (in diagram)

Tue, 05 Jan 2016 16:08:04 GMT

i think i will count on diagram. but it can be in script too.

yes, i have to know how much rows are there before the first B is in. I want to count the Bs as well, but only the uninterrupted Bs.

Example

In this example i would like to count the first As, then the First Bs, then de second As and the second Bs - ist that even possible?

Re: count until value changes (in diagram)

sunny_talwar — Tue, 05 Jan 2016 16:32:46 GMT

Script:

Table:

LOAD *,

If(Peek('Example') = Example, Peek('Same'), RangeSum(1, Peek('Same'))) as Same;

LOAD * Inline [

Example

];

Straight Table

Dimension: Example and Same

Expression: Count(Example)

Re: count until value changes (in diagram)

MarcoWedel — Wed, 06 Jan 2016 00:20:09 GMT

Hi,

maybe also helpful:

table1:
LOAD *,
    AutoNumber(ID,ClusterTotNum) as field1Seq,
    AutoNumber(ClusterTotNum,field1) as ClusterNum;
LOAD RecNo() as ID,
    *,
    Alt(Peek(ClusterTotNum),0)-(Previous(field1)<>field1) as ClusterTotNum
Inline [
field1
A
A
A
A
B
B
B
B
A
A
B
B
B
A
A
A
A
A
A
B
B
B
A
A
A
A
];

Join
LOAD ClusterTotNum,
    Count(field1) as ClusterSize
Resident table1
Group By ClusterTotNum;

Re: count until value changes (in diagram)

Wed, 06 Jan 2016 07:32:49 GMT

thank you! this will help me!

Re: count until value changes (in diagram)

Wed, 06 Jan 2016 07:33:08 GMT

thank you! this will help me!

Re: count until value changes (in diagram)

MarcoWedel — Thu, 07 Jan 2016 01:02:16 GMT

you're welcome.

If you like, you can mark this solution helpful.

thanks

regards

Marco

Re: count until value changes (in diagram)

Thu, 09 Feb 2017 13:49:54 GMT

Great! very helpful.

what if I want to count the number of clusters with the same label, output example:

A: 4

B: 3

Thanks!

Re: count until value changes (in diagram)

Thu, 09 Feb 2017 14:19:59 GMT

I figured it out by using the expression as max(ClusterNum):

This is very useful to count the number of clusters.

Re: count until value changes (in diagram)

MarcoWedel — Sun, 12 Feb 2017 01:29:41 GMT

Thanks.

Glad you liked it.

Another solution might be:

Count(Distinct ClusterNum)

regards

Marco