How to make a calculation using/retrieve data from different rows with similar column names

[Tian-Jay]

Hello,

I will go into as much detail as i can for clarity so sorry for the long post. What i am trying to do is create a calculation that will find the difference between two months performance values, for a set number of projects. The data is currently Column1-Project Column2-Year/Month data Column3-Performance, meaning that the project column may say the same project name 10+ times for each date.

I have a column called prev_month_inidicator which basically checks how long ago a certain months data was, this works fine (a value of 1 means 1 months ago data and a value of 2 means 2 months ago, etc)

I need to take Project A Month 2 data and subtract that from Project A Month 1 data, to find the change.

Currently i know ({<previous_month_indicator={1}>} [Performance])  will return the right number, but it will do this for every project on the list, as every project has Month1 and Month2 data. So when i try to do a calculation of ({<previous_month_indicator={2}>} [Performance]) -({<previous_month_indicator={1}>} [Performance]) for example, it returns a pretty meaningless result as it does this for every project and sums the result which is meaningless for me.

How do i make this calculation so it will calculate each unique project separately e.g. Germany's Month2-Month1 Data individually, but while still making calculations for the other projects, and also storing that value separately.

[Anil_Babu_Samineni]

It may be faster if you can start with sample data and expected result.

[Tian-Jay]

hello so visually this is what i am trying to do, the issue comes from that i am using the prev month column to indicate which rows to subtract but because that are multiple rows with the same value it doesn't work so well. after this i will need to find the average drop in the last 4 months, but i assume the code will be similar/easier for that

[Ahidhar · Accepted solution]

something like this ?

tab:
load
Project,date#("Year-Month",'MMM-YY') as "Year-Month",Performance
inline
[
Project,Year-Month,Performance
Germany,Aug-23,1.17
Germany,Sep-23,1.34
Germany,Oct-23,1.23
Germany,Nov-23,1.56
Canada,Aug-23,1.01
Canada,Sep-23,0.94
Canada,Oct-23,1.11
Canada,Nov-23,1.45
];

tab1:
load *,
if(Project=peek('Project',rowno(),'tab'),
Performance-peek('Performance',rowno(),'tab'),0) as diff
resident tab;drop table tab;

[Tian-Jay]

Yes this does solve the issue, Thank you!