Trying to calc month-over-month ratio of current vs past with a twist

[sergeyko]

Hi there,

I'm trying hard to build a chart that would show a monthly trend for the following ratio:

(sum of prices of items from previous month - sum of prices of items from current month * ) / sum of prices of items from previous month

Task A is to build a chart that will show this ratio for each month left in the list after a user selects Year(s) and Month(s) in filters. I'm struggling to define a "previous month" selection that would work for all available months. Tried the Above() function and it worked fine, with an exception of the first month, which I also want to cover if I have the data for the preceding month in my entire data set (regardless of applied filters).

Task B ( * ) is to limit the second component in the original ratio so that it only sums prices of items that existed in the previous month and caps the prices so that these do not exceed the prices of the same items from the previos month.

I'd appreciate any sugestions on both set expressions and data transforamtion approaches that would help me solve both assignments.

My data looks like as follows. Things to notice are: a) items vary between months, b) prices of same items vary between months.

Task A output for Jan 2017 will be (150+250+350 - 160+200+240) / 150+250+350

Task B turns out a bit different as (150+250+350 - 150[capped price of item 123]+0[item 124 was absent]+240[new price of item 125]) / 150+250+350

Year	Month	Item #	Price
2016	Dec	123	150
2016	Dec	125	250
2016	Dec	135	350
...	...	...	...
2017	Jan	123	160
2017	Jan	124	200
2017	Jan	125	240
…	…	…	…
2017	Feb	123	150
2017	Feb	124	220
2017	Feb	126	300
…	…	…	…

[sunny_talwar]

What are you chart dimensions? The best way to handle this is to use The As Of Table

[sergeyko]

Hi Sunny,

Thank you for the directions. My chart should show the calculated ratio through available months - nothing fancy there.

I'm already looking into how to adapt the powerful concept of AsOf table to my scenario.