count consecutive in table

[Anonymous]

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

IB

[felipe_dutra]

I had not read the end of the question.

The correct way would be via script.

Felipe

[datanibbler]

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

[Anonymous]

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

IB

[datanibbler]

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.

[Anonymous]

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

[Anonymous]

Thank u anyway, I appreciate!!!