Hi,
I trying to create a table chart for keyword rankings.
The chart will have 3 columns: Keyword, Rank(Last Month), Rank Change (MoM).
I am having trouble showing this in a table chart format. If the table is only supposed to show last month's Position, it filters out other dates - so how would the Rank Change MoM be calculated? The only way I managed to do this is by Uncollapsing Columns in ETL and moving dates in Columns, but this requires manual adjustment every month so it is not automated.
Some formulas I am using:
Rank (Last Month):
case when MONTH(`Date`)=MONTH(CURDATE())-1 then `Position` end
Rank Change MoM:
case when YEAR(CURDATE())=year(`Date`) AND MONTH(`Date`)=(MONTH(CURDATE())-1) then `Position` end
-
case when YEAR(CURDATE())=year(`Date`) AND MONTH(`Date`)=(MONTH(CURDATE())-2) then `Position` end
Below is an example of raw data:
Keyword | Position | Date |
test1 | 1 | 6/1/19 |
test1 | 3 | 5/31/19 |
test1 | 3 | 4/1/19 |
test1 | 1 | 3/31/19 |
test1 | 1 | 2/1/19 |
test1 | 1 | 1/31/19 |
test1 | 1 | 7/31/19 |
test2 | 2 | 6/1/19 |
test2 | 4 | 5/31/19 |
test2 | 3 | 4/1/19 |
test2 | 3 | 3/31/19 |
test2 | 2 | 2/1/19 |
test2 | 5 | 1/31/19 |
test2 | 4 | 7/31/19 |
Does anyone have a suggestion on how to make this possible, or if I am doing somehting wrong? thank you!