Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
… | … | … | … |
What are you chart dimensions? The best way to handle this is to use The As Of Table
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.