Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Thank you all in advance.
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
PFA. Hope meet your requirement.
Note: Please checked "Suppress When Value is Null".
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.