Hi,
Need a logic to create column H,I,J.
Attaching screenshot
Hi @Anurag2
maybe it would help all if you could share the logic of the columns.
BR
Hi,
Column B contains the version type that is updated monthly, and their corresponding values are listed in Column F, we want to calculate the monthly change in values for each version and display these changes in Column I.
For instance, if “ABC version 1” in January has a value of 3000 in Column F, “ABC version 2” in February has 4000, “ABC version 3” in March has 4500, and “ABC version 3” in April has 1500, you’re aiming to show in Column I that January’s value for “ABC version 1” remains 3000 (since there’s no previous month to compare to), but February’s value for “ABC version 2” would be the difference between February’s and January’s values (4000 - 3000 = 1000), and so forth, calculating the difference in values from one month to the next for each new version.
Now the column J will be sum of each version coming to from the respective month of column I.
And column K will be the difference between the value of current month to the value of last month of column J, and first month will be the same of column J as their is not previous month.
Hi @Anurag2,
Based on the information you have provided, it seems like the lag function would provide the aggregation level you are looking for. It would allow you to bring values from previous dates on the same row as the current date and compare them. I’ll link documentation below for your review:
Alternatively, you could also try something like periodOverPeriodDifference with partitions based on the column returning ‘ABC Version 1’ to compare values from one day to the next:
I’ll mark this as the solution but if you have any further questions, feel free to create a new topic.
Thank you!