how to calculate current sales status(Growth or drop) compared with previous year sales

[grajmca_sgp123]

hi,

I have a requirement to to calculate "Status" fields based on CurYear vs PreYeas sales values.

Here below is the scenario,can you please provide your suggestion on it.

compare with previous sales values with current year sales values,if current year sales values are higher than previous year sales values, show status as growth else show it as drop.

dimA	dimB	year	sales	Status
A1	B1	2019	100
A1	B1	2020	200	growth
A3	B3	2019	5000
A3	B3	2020	2000	drop
A4	B4	2019	1000
A4	B4	2020	70000	growth

[grajmca_sgp123]

I have done it by using set analysis,but I want to calculate this Status field in script level.

using set analysis:

if( Sum({$<Year = {"$(=$(vCurrentYear))"},MonthNum = {"<$(=$(vCurrentMonth))"}>} [Profit (USD)])
> Sum({$<Year = {"$(=$(vCurrentYear)-1)"},MonthNum = {"<$(=$(vCurrentMonth))"}>} [Profit (USD)]),'Growth','Drop'
)

[Taoufiq_Zarra]

@grajmca_sgp123  One option in script :

Data:
LOAD * INLINE [
    dimA, dimB, year, sales
    A1, B1, 2019, 100
    A1, B1, 2020, 200
    A3, B3, 2019, 5000
    A3, B3, 2020, 2000
    A4, B4, 2019, 1000
    A4, B4, 2020, 70000
];

output:

load *,if(dimA=peek(dimA) and dimA=peek(dimA),if(peek(sales)<sales,'growth','drop')) as Status resident Data order by dimA,dimB,year;

drop table Data;

output: