A twist in the ratio I need to calculate is that it is not just current / past, but the current must only include items that existed in the past month and sum their prices capped to the prices of the same items from the previous month.
My data looks like as follows:
My ratio for Jan 2017 looks like (sum of past month) - (limited sum of current month) / (sum of past month)
Hence, (160+240+400) - (150+240[price of item 125 is capped to its previous amount]+0[item 135 is missing in past]) / (160+240+400)
I think I need to transform my data to introduce a couple of new columns:
a) The one that will say whether the current item has its duplicate in the past month
b) The other that will store capped prices of items
I would appreciate your suggestion on how to make those transformations on the data loading stage. If you have ideas how to make it work on the frontend, I am certainly open to them as well.
That looks like a way to go. I'm going to mark your last reply as a solution to my original problem.
I'm also exprimenting with building an AsOf table through joining the original table to itself so that I can add additional columns and easily manipulate the data afterwards.
