chilly_bang
Board Regular
- Joined
- Jun 17, 2016
- Messages
- 57
I have following data:
These data should be placed in a table so i can with a formula calculate the prozentual difference of metric between monthes for the certain url.
The following is an example of the table. If you mean, the table should better look otherwise (transponded, sorted on the other way or so) to enable smart calculation - just say!
I firstly thought, i calculate the difference of the secod month to the first with the formula like
- Date (year and month, always the first day, YYYY-MM-DD)
- URL
- Metric (number)
These data should be placed in a table so i can with a formula calculate the prozentual difference of metric between monthes for the certain url.
The following is an example of the table. If you mean, the table should better look otherwise (transponded, sorted on the other way or so) to enable smart calculation - just say!
Code:
+------------+------+--------+----------------------------------+
| A | B | C | D |
+------------+------+--------+----------------------------------+
| Date | URL | Metric | Metric: Change to previous month |
+------------+------+--------+----------------------------------+
| 2019-06-01 | url1 | 1312 | |
+------------+------+--------+----------------------------------+
| 2019-07-01 | url1 | 1241 | |
+------------+------+--------+----------------------------------+
| 2019-08-01 | url1 | 5370 | |
+------------+------+--------+----------------------------------+
| 2019-09-01 | url1 | 6117 | |
+------------+------+--------+----------------------------------+
| 2019-10-01 | url1 | 5827 | |
+------------+------+--------+----------------------------------+
| 2019-11-01 | url1 | 4827 | |
+------------+------+--------+----------------------------------+
| 2019-12-01 | url1 | 5267 | |
+------------+------+--------+----------------------------------+
| 2020-01-01 | url2 | 7218 | |
+------------+------+--------+----------------------------------+
| 2020-02-01 | url2 | 6440 | |
+------------+------+--------+----------------------------------+
| 2020-03-01 | url2 | 5966 | |
+------------+------+--------+----------------------------------+
| 2020-04-01 | url2 | 5283 | |
+------------+------+--------+----------------------------------+
| 2020-05-01 | url2 | 6020 | |
+------------+------+--------+----------------------------------+
I firstly thought, i calculate the difference of the secod month to the first with the formula like
=c3/c2-1
and get the procentual difference. But then i realized two problems:- the first month should be excluded from calculation because there is nothing, what can be compared with the first month's value.
- there are multiple urls, and the formula must "know", that calculations should appear only applied to same url's metrics.