3 Replies Latest reply: May 14, 2014 4:45 AM by Dimitris Charalampou

# calculate the maximum number of consecutive months where a field is equal to zero

Hi to all,

I have a table with 3 fields like the example below.

I want to calculate during load for each id, the maximum number of consecutive months where the transactions field is equal to zero.

So for this example , for id 1 the value should be equal to 4 and for the id 2 it should be equal to 3.

 id month transactions 1 January 12 1 February 32 1 March 0 1 April 0 1 May 0 1 June 0 1 July 43 2 March 0 2 April 0 2 May 0 2 June 23 2 July 555 2 August 0

Any suggestions?

• ###### Re: calculate the maximum number of consecutive months where a field is equal to zero

if(previous(transactions)=transactions and transactions=previous(transactions) and transactions=0,count( distinct Month)

or

count(if(previous(transactions)=transactions and transactions=previous(transactions) and transactions=0, distinct Month)

hope this helps

• ###### Re: calculate the maximum number of consecutive months where a field is equal to zero

Note: Please checked "Suppress When Value is Null".

• ###### Re: calculate the maximum number of consecutive months where a field is equal to zero

Hi there,

Thank  you for your quick reply. Unfortunately the qvw you included is not what i want to achieve.

What would be great for me is to add a new collumn (let's call it, "Max consecutive month flag") next to the table during load time, not in a chart afterwards, where only for those months that are in the maximun consecutive period the value will be equal to 1 and for all the rest months it would be equal to zero. If a month ihas a zero value in the  transactions field but it is not in the maximun consecutive perdiod the value of this flag should be equal to zero, in this example the record for id = "2" and month = "August" (the last record of my example).

I hope i did not confuse you with my analysis.

Thank you again.