Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
It may be faster if you can start with sample data and expected result.
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
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;
Yes this does solve the issue, Thank you!