topic Re: count consecutive in table in QlikView

count consecutive in table

Anonymous — Thu, 12 Jun 2014 11:41:01 GMT

Hello Community,

do you know how to count consecutive values in a straight or pivot table??? I mean, suppose you have a straight table like the following one:

year month result

2013 01 ARIMA

2013 02 ARIMA

2013 03 ARIMA

2013 04 SERIES

2013 05 SERIES

2013 06 ARIMA

2013 07 ARIMA

2013 08 OTHER

2013 09 ARIMA

2014 01 ARIMA

2014 02 ARIMA

2014 03 OTHER

2014 04 SERIES

I'd like to have such a resulting table, with counting results aggr per Year:

2013 3

2014 2

that is 2013 has the first 3 values equals to each other, while the following are different, so I don't need them; 2014 has its first 2 values equals to each other, while the others are different...

is it possible to do that without macros? maybe using aggr and above???

thank u all guys

greetings

Re: count consecutive in table

felipe_dutra — Thu, 12 Jun 2014 11:47:55 GMT

I had not read the end of the question.

The correct way would be via script.

Felipe

Re: count consecutive in table

datanibbler — Thu, 12 Jun 2014 11:51:38 GMT

Hi Irene,

sure it's possible.

You have to do it in the script, using the PEEK() or PREVIOUS() function.

- Generate a binary field (1 or 0)

- Generate a condition stating that

- the record above must (in that "result" field) have the same value as the current one

- the value (in the newly created field) in the record above must be 1.

- You have to modify the condition for the very first record (just put a static 1 in that one) since there is none above, so the PREVIOUS() fct will fail there -> and in all consecutive records, too, if you don't account for that.

HTH

Best regards,

DataNibbler

Re: count consecutive in table

Anonymous — Thu, 12 Jun 2014 11:58:37 GMT

Hi DataNibbler,

I've already heard about that, unfortunately the values in result may vary with selections, that is Year and Month are not simple fields but are calculated to satisfy a certain interval of time, so after a selection I may find out the table I've illustrated but after another selection maybe I would have less months to analyze...

What can I do? Nothing's possible in presentation?

Thanks

Re: count consecutive in table

datanibbler — Thu, 12 Jun 2014 12:03:35 GMT

Hi Irene,

so that table is not in the script, but it is only generated in this form on the GUI based on the user's selections?

Well, that makes it more complicated as the PEEK() or PREVIOUS() fcts are not available on the GUI.

I'm not aware of any possibility then. Sorry.

Re: count consecutive in table

Anonymous — Thu, 12 Jun 2014 12:16:53 GMT

Hi,

This can be achieved using rangesum and previous function:

if(previous(Year)=Year and previous(result)=result, RangeSum(1,Peek(count)), 1) as count

couunt will be initially having 1 and if the condition satisfies, 1 will be added to the previous count value

Re: count consecutive in table

Anonymous — Mon, 16 Jun 2014 13:45:28 GMT

Thank u anyway, I appreciate!!!