status on the basis of last & current month data

[Anonymous]

HI All,

I am facing an issue in calculating certain field

All the Id's has different status for each month ,we need to calculate new status which will be calculated on the basis of status of Id comparison with its last month status

if  current month status is  A and   last month status is B - then UP is my Status New

if  A-B - then UP
if B-C then UP
if A-C then UP
if C-A then DOWN
if B-A then DOWN
if B-A then UP
if C-B then DOWN

Please find the sample file

[tresesco]

May be like this?

If(RowNo()=1,'NA', If(Peek(Status)<Status, 'UP', 'DOWN')) as "New Status"

[kevinchevrier]

May be something like this in your script :

If(PREVIOUS(Status) = 'A', If(Status= 'B' or Status = 'C', 'UP'),

If(PREVIOUS(Status) = 'B', If(Statuts = 'A', 'DOWN', if(Statuts = 'C', 'UP')),

If(PREVIOUS(Status) = 'C', If(Statuts = 'A' or Status = 'B', 'DOWN')) as "New Status"

or :

If(pick(previous(Status),1,2,3)>pick(Status,1,2,3),'DOWN','UP') as "New Status"

[kevinchevrier]

Else Tresesco B is right.

I made your table in the file attached. Is it ok ?

[Anonymous]

Hi Tresesco ,

Thanks for your reply . I am facing an issue while implementing your logic in my real scenario .So I am sharing you the sample file of my real scenario .Please help me out to implement the same

[Anonymous]

Also need to count ID's with 'UP'  as New Status

[Anonymous]

Thanks for your reply .I have add my  real scenario .Do you have any idea

[mahendragaur]

Hello Shweta,

Please find the attached file. I think this is what you were expecting.

[Anonymous]

Can we implement the same using  applymap