Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Count by values

Hi community,

I need to calculate a counting in a simple table chart using the values of one the columns, which values are not unique.

I want a result that look like the following example

XX

Counting

A

1

A

2

A

3

B

1

B

2

C

1

D

1

D

2

I tried to use rowno(TOTAL) but that is not the solution for my needs.

I will really appreciate any hint to solve that

Thanks in advance.

Ivan

1 Solution

Accepted Solutions
rubenmarin

Hi Ivan, a chart will group values based on the dimensións used, if there is only one dimension, there will be only one value per row, so I think you will need at least a second dimensión wich tells why to paint 3 different 'A'.

In script you can add this counter using a sorted load and Peek() or Previous():

LOAD XX,

  If(Peek('XX')=XX, RangeSum(Peek('Counting'), 1), 1) as Counting

Resident TableName

Order By XX, OtherSortingFields;

View solution in original post

3 Replies
rubenmarin

Hi Ivan, a chart will group values based on the dimensións used, if there is only one dimension, there will be only one value per row, so I think you will need at least a second dimensión wich tells why to paint 3 different 'A'.

In script you can add this counter using a sorted load and Peek() or Previous():

LOAD XX,

  If(Peek('XX')=XX, RangeSum(Peek('Counting'), 1), 1) as Counting

Resident TableName

Order By XX, OtherSortingFields;

sunny_talwar

Although not as efficient as Peek()/Previous() pointed out by rubenmarin‌, but AutoNumber is another possible solution for the script

LOAD XX,

     AutoNumber(RowNo(), XX) as Counting

Reisdent ....

Order by XX;

vishsaggi
Champion III
Champion III

Another expression using Ruben's Peek().

Check this.

Table1:

LOAD * INLINE [

XX

A

A

A

B

B

C

D

D

];

LEFT JOIN(Table1)

Table2:

LOAD *, IF(XX = Previous(XX), Peek(Counting)+1, 1) AS Counting

Resident Table1

Order By XX;