Hi all, I am trying to find the number of months with consecutive decreases in sales. For example, ID Month Sales 1 Jan 100 1 Feb 120 1 Mar 90 1 Apr 80 1 May 70 1 Jun 110 2 Jan 150 2 Feb 150 2 Mar 100 2 Apr 90 2 May 110 2 Jun 100 Hence, I use LAG function to get the previous sales and assign 1 if it decreases and 0 if it remains or increases. ID Month Sales PrevSales Decreasecount 1 Jan 100 . 0 1 Feb 120 100 0 1 Mar 90 120 1 1 Apr 80 90 1 1 May 70 80 1 1 Jun 110 70 0 2 Jan 150 . 0 2 Feb 150 150 0 2 Mar 100 150 1 2 Apr 90 100 1 2 May 110 90 0 2 Jun 100 110 1 For ID 1, it should be 3 months. And for ID 2, it should be 2 because it decreases consecutively then increases, then decreases again. Hence, ignoring the 2nd time it decreases. If I sum the decreasecount, it might not be consecutive. Hence, how can I find the number of months with consecutive decreases in sales only? Appreciate if anyone can help! Thank you!
... View more