Forum Discussion
Issue status log to status on a day
I have data log table which i need to present in a status per day. The status log table has the following format:
| Date | Issue | Status |
| 3-jan | A | Todo |
| 15-jan | A | InProgress |
| 20-jan | A | InReview |
| 22-jan | A | Done |
| 6-jan | A | Todo |
| 18-jan | A | InProgress |
| 28-jan | A | InReview |
| 2-feb | A | Done |
I like to have information how many issues have a certain status on a day:
| Date | Todo | InProgress | InReview | Done |
| 1-jan | ||||
| 2-jan | ||||
| 3-jan | 1 | |||
| 4-jan | 1 | |||
| 5-jan | 1 | |||
| 6-jan | 2 | |||
| 7-jan | 2 | |||
| 8-jan | 2 | |||
| 9-jan | 2 | |||
| 10-jan | 2 | |||
| 11-jan | 2 | |||
| 12-jan | 2 | |||
| 13-jan | 2 | |||
| 14-jan | 2 | |||
| 15-jan | 2 | |||
| 16-jan | 1 | 1 | ||
| 17-jan | 1 | 1 | ||
| 18-jan | 2 | |||
| 19-jan | 2 | |||
| 20-jan | 1 | 1 | ||
| 21-jan | 1 | 1 | ||
| 22-jan | 1 | 1 | ||
| 23-jan | 1 | 1 | ||
| 24-jan | 1 | 1 | ||
| 25-jan | 1 | 1 | ||
| 26-jan | 1 | 1 | ||
| 27-jan | 1 | 1 | ||
| 28-jan | 1 | 1 | ||
| 29-jan | 1 | 1 | ||
| 30-jan | 1 | 1 | ||
| 31-jan | 1 | 1 | ||
| 1-feb | 1 | 1 | ||
| 2-feb | 2 | |||
| 3-feb | 2 | |||
| 4-feb | 2 |
The information statuslog information comes from a database. I am able to load it into Excel. The period which I like to analyze is not flexible.
I prefer to use pivot tables for this, but I am not able to load the information from the status log table direct into the pivot table.
Any suggestion?
Here is another attempt. To generate the log separate Calendar table is created with first date and number of dates as parameter. Status table is merged with it and log for each code is generated by code inside. Not sure that's optimal from performance point of view and if I understood all logic of log correctly, but that's first what was in mind.
Please check attached file.
5 Replies
That could be done with Power Query, please see the mockup attached.
Assumptions are
- first date in the log is with zeros (could be corrected)
- status is changed if the number for next date is more than for previous
Thanks for your quick response, in the attached I see that the second table is transferred into the first status log table. But I need the have this the other way around.
I have a status log table and need to transfer it into the number of statusses per day table. Is this also possible with power query?
Here is another attempt. To generate the log separate Calendar table is created with first date and number of dates as parameter. Status table is merged with it and log for each code is generated by code inside. Not sure that's optimal from performance point of view and if I understood all logic of log correctly, but that's first what was in mind.
Please check attached file.
