Identify Increasing or Decreasing Trend 2

[BTrees]

Hi All,

I have data like
EmpID Salary Paid Date Salary Paid
100 627.8 1/11/2019
100 1085.56 1/25/2019
100 1055.01 2/8/2019
100 980.6 2/22/2019
100 653.35 3/8/2019
100 870.94 3/22/2019
100 385.2 4/18/2019
101 2032 1/4/2019
101 3466 1/18/2019
101 2652 2/1/2019
101 2013 4/26/2019


Is there any way identify a "Trend Decreasing" flag (Yes/ No). only one record or preferably decision on "Decreasing" can be repeat for all the rows. something like

EmpID Salary Paid Date Salary Paid Decreasing Flag
100 628 1/11/2019 Y
100 1086 1/25/2019 Y
100 1055 2/8/2019 Y
100 981 2/22/2019 Y
100 653 3/8/2019 Y
100 871 3/22/2019 Y
100 385 4/18/2019 Y
101 2032 1/4/2019 N
101 3466 1/18/2019 N
101 2652 2/1/2019 N
101 2013 4/26/2019 N

Y/N is the valued for each empid (not for each row)

Thanks

 

[johnherman]

I am not clear on what you want to happen. To me, it looks like EmpID 101 has decreasing amounts of Salary_Paid after 1/18/2019.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[BTrees]

Hi John. Thanks for your reply. I want to know the trend is it decreasing or increasing over the period of time. Since an employee have random amount of salaries during last 6 months on different dates. At one point it is very high and other low. what is the trend of his salary, if it's increasing or decreasing. Hope this will help. thanks

 

[johnherman]

Again my question. Both examples have ups and downs. Both examples have an downward trend when comparing the first observation/record to the most recent one. Both examples have a downward trend when comparing the most recent to the next prior record. So, from my perspective, both examples should have the same value for the "Decreasing Flag", BUT THEY DON'T. What is it that you are trying to do?

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[BTrees]

Hi John, sorry for misconception rather my mistake, the 'decreasing flag' has just sample value (the way results should be displayed). I just trying to identify a way which will calculate/determine this flag value. Hope this explanation will help. Thanks

 

[SkipVought]

How are you defining a decreasing trend?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[johnherman]

To follow up on Skip's question (which follow from my earlier questions), how do you calculate the value for the DecreasingFlag for the first (earliest) record for each EmpID?

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[BTrees]

Hi John, this is actually my question. I mentioned history of salary paid. sometimes increasing and sometime decreasing. Jut like you can plot on chart and get a slope to see visually trend, salary is increasing trend or decreasing, I need to figure out in terms of yes no values .

 

[SkipVought]

But there are all kinds of ways to calculate a trend. YOU, your driving requirement, the marching orders of the person(s) requesting this feature, must be the source. We can only guess at what it might be. There is no single answer!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

Just a guess here...

If you order your data by [tt]EmpID[/tt] and [tt]Date Salary Paid[/tt], you may get something like this:

[pre]
EmpID Salary Paid Date Salary Paid Decreasing Flag
100 628 1/11/2019
100 1086 1/25/2019 N
100 1055 2/8/2019 Y
100 981 2/22/2019 Y
100 653 3/8/2019 Y
100 871 3/22/2019 N
100 385 4/18/2019 Y
101 2032 1/4/2019
101 3466 1/18/2019 N
101 2652 2/1/2019 Y
101 2013 4/26/2019 Y
[/pre]
Where you have Y when salary decreases, and N when increases.
First records for employee does not have anything since there is nothing to compare it to, no 'previous' record.

As far as "decision on "Decreasing" can be repeat for all the rows" - you may do that if you have more Y's than N's, but what do you do when the number of Y's = number of N's? You may do some fancy calculations in addition to what I guessed here, but like others said: YOU need to provide the rule.


---- Andy

There is a great need for a sarcasm font.

 

[johnherman]

I suspect that Andy's interpretation of your requirements will be what you need. When you are defining a trend, you need to specify what the most recent value is compared against. In his example, it's the most recent prior record. It could also be compared to the record for last week, last month, last quarter, last year, the first value, etc. THAT is what we were trying to get you to tell us.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[Andrzejek]

John,

I suspect that Andy's interpretation of your requirements will be what you need.

That may not be the case. Let’s say I get paid twice a month and the data looks like this:

[pre]
Date Paid Amount Decreasing Flag
1/1/2019 1000
1/15/2019 999 Y
2/1/2019 888 Y
2/15/2019 777 Y
3/1/2019 5000 N
3/15/2019 4999 Y
4/1/2019 4888 Y
4/15/2019 4777 Y
5/1/2019 10000 N
5/15/2019 9999 Y
6/1/2019 9888 Y
6/15/2019 9777 Y
[/pre]
So over all, my Decreasing Flag is (mostly) Y, but who wouldn’t want to have a ‘decrease’ in salary like this…?



---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

I'd use a moving average using x number of data points.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[johnherman]

I actually *had* a salary like that once. The payroll system was being changed from hourly rate to daily rate. The person entering my data entered 40 (days) instead of 40 hours (aka 5 days). Weekly salary was 8 times what it should have been. Being a nice guy, I told them before they discovered it themselves.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity