Forum Discussion
Visualising best time to call
Community SupportHi marzio ,
For per ID, are there multiple timestamps in the same row?
I am not sure what desired result would you want, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.
Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Amy,
thank you for your answer.
Yes, there are multiple time stamps per row. You can see down below a small redacted sample of the data.
The source is a data dump with more than 200 columns that I would rather not upload.
Starting from 2018 and onwards.
Every set of columns starting with FC1,FC2, SC1 is a different call attempt with different outcomes. In total there are max 13 time stamps per row (3 for every FC call, 10 for every SC call).
I would like to visualise what is the best time for attempting a call based on a successful status. Both being able to see all FC attempts together, but also slicing for FC1, FC2, FC3 or time period. Using some predefined time intervals (20-30 minutes).
The output would be something like this with the green line being successful and orange not successful attempts.
I have tried to define which steps I would need to model the data but I have not been able to find all the sources to create them for PowerBI. The second point is the most troublesome.
- Create time intervals table (http://datacornering.com/how-to-generate-date-and-time-interval-list-using-power-query/)
- Link time stamps in source data to time intervals table
(does this require splitting time stamps into different rows? Do I have to add extra columns to the source file?)
- Calculate number of time stamps for every interval
- Add data to chart/graph to visualize in the best way
I hope you can help me.
Marzio
| UPLOADED_DATE | FC1_STATUS | FC1_UNSUCCESSFUL_STATUS | FC1_ATTEMPTED_DATE | FC1_CALLBACK_DATE | FC2_STATUS | FC2_UNSUCCESSFUL_STATUS | FC2_ATTEMPTED_DATE | FC2_CALLBACK_DATE | FC3_STATUS | FC3_UNSUCCESSFUL_STATUS | FC3_ATTEMPTED_DATE | FC3_CALLBACK_DATE | SC1_STATUS | SC1_UNSUCCESSFUL_STATUS | SC1_ATTEMPTED_DATE | SC1_CALLBACK_DATE | SC2_STATUS | SC2_UNSUCCESSFUL_STATUS | SC2_ATTEMPTED_DATE | SC2_CALLBACK_DATE | SC3_STATUS | SC3_UNSUCCESSFUL_STATUS | SC3_ATTEMPTED_DATE | SC3_CALLBACK_DATE | SC4_STATUS | SC4_UNSUCCESSFUL_STATUS | SC4_ATTEMPTED_BY | SC4_ATTEMPTED_DATE | SC4_CALLBACK_DATE | SC4_COMMENTS | SC5_STATUS | SC5_UNSUCCESSFUL_STATUS | SC5_ATTEMPTED_BY | SC5_ATTEMPTED_DATE | SC5_CALLBACK_DATE | SC5_COMMENTS | SC6_STATUS | SC6_UNSUCCESSFUL_STATUS | SC6_ATTEMPTED_BY | SC6_ATTEMPTED_DATE | SC6_CALLBACK_DATE | SC6_COMMENTS | SC7_STATUS | SC7_UNSUCCESSFUL_STATUS | SC7_ATTEMPTED_BY | SC7_ATTEMPTED_DATE | SC7_CALLBACK_DATE | SC7_COMMENTS | SC8_STATUS | SC8_UNSUCCESSFUL_STATUS | SC8_ATTEMPTED_BY | SC8_ATTEMPTED_DATE | SC8_CALLBACK_DATE | SC8_COMMENTS | SC9_STATUS | SC9_UNSUCCESSFUL_STATUS | SC9_ATTEMPTED_BY | SC9_ATTEMPTED_DATE | SC9_CALLBACK_DATE | SC9_COMMENTS | SC10_STATUS | SC10_UNSUCCESSFUL_STATUS | SC10_ATTEMPTED_BY | SC10_ATTEMPTED_DATE | SC10_CALLBACK_DATE | SC10_COMMENTS | |
| A5572197 | 07-08-19 01:06:00 | Successful | 8-7-2019 9:08 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| A5418573 | 09-07-19 03:30:00 | Successful | 7-9-2019 9:59 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| A5418574 | 09-07-19 03:30:00 | Successful | 7-9-2019 9:57 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| A5418575 | 09-07-19 03:30:00 | Successful | 7-9-2019 9:57 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| A5553728 | 02-08-19 05:53:00 | Successful | 8-2-2019 10:40 | Successful | 8-5-2019 14:43 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| A5553726 | 02-08-19 05:53:00 | UnSuccessful | Ringing | 8-2-2019 11:27 | UnSuccessful | Ringing | 8-2-2019 12:59 | UnSuccessful | Ringing | 8-5-2019 9:56 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| A5519141 | 26-07-19 05:52:00 | UnSuccessful | Callback | 7-26-2019 10:01 | 7-26-2019 11:01 | UnSuccessful | Ringing | 7-26-2019 11:02 | Successful | 7-26-2019 12:09 | UnSuccessful | Voicemail | 8-1-2019 12:18 | UnSuccessful | Callback | 8-1-2019 14:05 | 8-5-2019 10:00 | UnSuccessful | No call for this time | 8-7-2019 11:05 |
|
- v-xicai
Hi marzio ,
You may need to unpivot the same call type , call timestamp and call outcome into a separate columns.
For example, enter into Query Editor, click on columns call 1 timestamp - call N timestamp at the same time, then choose "Unpivot Columns" option, you can rename this result column "Attribute" with "call timestamp" . Don't forget to click the "Close & Apply" button. Then you can create relationship between the source table and the data to time intervals table on date field.
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Amy,
The Unpivot solution is going to the right direction but doing it for the data source multiplies every row, and that causes many issues to the rest of the data. All the charts and calculations are showing wrong data so I had to backtrack.
I have tried to SUMMARIZE the columns I need into a separate table but I cannot UNPIVOT that because it is a DAX function.
The step I am missing to make it work is creating a new table in Power Query based on a selection of columns.
So I can UNPIVOT the columns and follow the rest of your suggestion.
Any ideas for that step?
I got lost in other threads without finding a working solution.
Marzio