As I tried to create a new column Progression with a existing table, as in the below and what I am expecting for instance, is for the customer 1 the NetSalesYear which should be compared with Year if the maximum of year is high then it should write as "UP" if its less than with the previous year then "Down" in the script I would like to write.
Jagans logic is more error proof than mine, but the principle is the same.
It relies on the data in Test_TMP being in the correct order (customer ascending then year ascending).
The 'previous' command allows you to look at the previous record in the source table (in this case test_TMP). The 2 levels of If statement are needed to handle the change in customerId before checking the previous year sales value.
As you can see there are many ways to express the IF statements but they are fundamentally the same.
Jagans logic is more error proof than mine, but the principle is the same.
It relies on the data in Test_TMP being in the correct order (customer ascending then year ascending).
The 'previous' command allows you to look at the previous record in the source table (in this case test_TMP). The 2 levels of If statement are needed to handle the change in customerId before checking the previous year sales value.
As you can see there are many ways to express the IF statements but they are fundamentally the same.
Using Previou() you will get the previous record value, we are comparing the previous record customer and current record customer if not equals then it is first record and we are marking it as 'N/A', if same then we are comparing the NetSalesByYear >= Previous(NetSalesByYear) if it satisfies then UP else DOWN.
Test_TMP:
LOAD * Inline [
CustomerID,Year,NetSalesByYear
1,2003, 1000
1,2004, 2000
2,2003, 500
2,2004, 200
3,2004, 10000
4,2003, 15000
4,2004, 1200
];
For example in the above for second record Previous(NetSalesByYear) will get 1000.
.png "Luminary Alumni"){kind=icon}