Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
chauhans85
Esteemed Contributor

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

senpradip007
Valued Contributor III

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

PFA. Hope meet your requirement.

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

Not applicable

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.

Community Browser