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: 
Not applicable

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.

idmonthtransactions
1January12
1February32
1March0
1April0
1May0
1June0
1July43
2March0
2April0
2May0
2June23
2July555
2August0

Any suggestions?

Thank you all in advance.

3 Replies
SunilChauhan
Champion
Champion

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

Sunil Chauhan
senpradip007
Specialist III
Specialist III

PFA. Hope meet your requirement.

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

Not applicable
Author

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.