I am doing a time series analysis for ticket created over time and would like to monitor consecutive concurrent decrease month over month for our clients. Below is my original data set: Open Date N of Clients Sum of Tickets 01NOV14:00:00:00 1070 3530 01DEC14:00:00:00 1448 8486 01JAN15:00:00:00 1821 23580 01FEB15:00:00:00 1796 20648 01MAR15:00:00:00 1756 22872 01APR15:00:00:00 1789 21601 01MAY15:00:00:00 1746 20059 01JUN15:00:00:00 1757 12946 01JUL15:00:00:00 1780 22874 01AUG15:00:00:00 1799 24045 01SEP15:00:00:00 1855 27344 01OCT15:00:00:00 1777 23124 01NOV15:00:00:00 1682 17861 01DEC15:00:00:00 1868 19191 If there is a one month decrease in sum of tickets, then clients will receive a score of 0.9. If there are a two-month decrease in sum of tickets, then clients will receive a score of 0.7 and so on until the score is 0.1. When there is a one month increase in sum of tickets, the score should return to the prior level. The new data should look like this: Open Date N of Clients Sum of Tickets Consecutive 01NOV14:00:00:00 1070 3530 01DEC14:00:00:00 1448 8486 1 01JAN15:00:00:00 1821 23580 1 01FEB15:00:00:00 1796 20648 0.9 01MAR15:00:00:00 1756 22872 1 01APR15:00:00:00 1789 21601 0.9 01MAY15:00:00:00 1746 20059 0.7 01JUN15:00:00:00 1757 12946 0.5 01JUL15:00:00:00 1780 22874 0.7 01AUG15:00:00:00 1799 24045 0.9 01SEP15:00:00:00 1855 27344 1 01OCT15:00:00:00 1777 23124 0.9 01NOV15:00:00:00 1682 17861 0.7 01DEC15:00:00:00 1868 19191 0.9 I have tried different step by using do or LAG but can not code the consecutive score correct. My score always returns to 1 instead of the prior level when there is an increase. Can somebody help me on this programming? Thanks a lot!
... View more