Forum Discussion
Tim intelligence: % Change, Overall Usage %, & Position Change
I'm hoping someone can assist. I have a table of document reference numbers, datestamps of when they were validated ("Hits"), and who validated them, as part of a support process:
| DocId | Hit User | Hit Date | Hit User Department |
| 400029 | Jimmy James | 23/09/2019 10:43 | Sales |
| 400029 | Jimmy James | 21/08/2019 10:43 | Sales |
| 400029 | Kelly kellyson | 23/09/2019 10:43 | Finance |
| 400029 | Kelly kellyson | 23/09/2019 10:43 | Finance |
| 400029 | Peter Peterson | 4/08/2019 12:05 | Services |
| 400030 | Peter Peterson | 4/08/2019 12:05 | Services |
| 400030 | Petra Petrason | 23/09/2019 10:43 | C-Suite |
| 400030 | Sarah Sarahson | 4/08/2019 12:05 | Finance |
| 400030 | Sarah Sarahson | 21/08/2019 10:43 | Finance |
| 400030 | Jimmy James | 23/09/2019 10:43 | Sales |
| 400030 | Kelly kellyson | 4/08/2019 12:05 | Finance |
| 400030 | Kenny Kennyson | 23/09/2019 10:43 | Sales |
| 400030 | Kenny Kennyson | 21/08/2019 10:43 | Sales |
| 400030 | Mark Markson | 4/08/2019 12:05 | Sales |
| 400030 | Mark Markson | 23/09/2019 10:43 | Sales |
| 400035 | Michael Hutchens | 4/08/2019 12:05 | Services |
| 400035 | Peter Peterson | 23/09/2019 10:43 | Services |
| 400035 | Petra Petrason | 4/08/2019 12:05 | C-Suite |
| 400035 | Petra Petrason | 21/08/2019 10:43 | C-Suite |
| 400035 | Petra Petrason | 4/08/2019 12:05 | C-Suite |
| 400035 | Petra Petrason | 23/09/2019 10:43 | C-Suite |
| 400035 | Sarah Sarahson | 21/08/2019 10:43 | Finance |
I'd like to be able to create a table that shows the following:
| DocID | Total Hits Last Month | % Change Since Month -2 | % Usage Last Month | Position Change From Month -2 |
| 400029 | 3 | +150% | 33% | +1 |
| 400030 | 4 | -66% | 44% | -- |
| 400035 | 2 | -40% | 22% | -1 |
Rules
- There is no particular order that the table rows are in - they could be all mixed up, or sorted by one or another column;
- In this example I'm using September 2019 as 'Last Month' and August 2019 as 'Month -2';
- % Usage Last Month = hits for the Doc last month, as a % of total hits for all articles in the top 20 (also from last month);
- Position Change From Month -2 = Ranking change for each DocID in the top 20. If no ranking change then it should be "--", and if the DocID is new to the top 20, then it should state "New";
- There is no particular order that the table rows are in - they could be all mixed up, or sorted by one or another column;
- In this example I'm using September 2019 as 'Last Month' and August 2019 as 'Month -2';
- % Usage Last Month = hits for the Doc last month, as a % of total hits for all articles in the top 20;
- Position Change From Month -2 = Ranking change for each DocID in the top 20. If no ranking change then it should be "--", and if the DocID is new to the top 20, then it should state "New".
Any help would be greatly appreciated!
Hi mhutchens81,
You could refer to my sample file to see whether it work or not.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi mhutchens81,
You could refer to my sample file to see whether it work or not.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi dax , that looks to be 95% of what I need - I really appreciate your quick response!
Would it be possible to avoid hard-coding month names and numbers into the calculations? That way I can use this as a rolling monthly report as it would refer to 'last month' and 'the month before last month', rather than 'August' or 'month 8' (for example) specifically:
Hi
You could use below expression to replace september(9)
MONTH(TODAY())-1
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.