Visualising best time to call

Question

Hello,&nbsp;I have a large dataset of calls of a call center grouped via a unique identifier ID.Each row has a single ID and (among many others) multiple columns with the time stamp of each call attempt dd-mm-yyyy hh-mm-ss.&nbsp;&nbsp;e.g.: ID | call 1 type | call 1 timestamp | call 1 outcome | call 2 type | call 2 time stamp | call 2 outcome | ... | call 13 type | call 13 time stamp | call 13 outcome| ...&nbsp;I would like to visualize a distribution of the the best time to call in 20/30 minutes intervals based on the outcome. And be able to slice it for the different call type.&nbsp;Any suggestion on how to proceed?&nbsp;&nbsp;Many of the threads I have found have a dataset with one time stamp per row but I need my source file to remain in the format it is now.&nbsp;I hope some of you can help a complete noob :)&nbsp;&nbsp;&nbsp;

v-xicai · Answer

Hi marzio&nbsp;,
&nbsp;
For per ID, are there multiple timestamps in the same row?
&nbsp;
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&nbsp;and post&nbsp;the link here. Do mask sensitive data before uploading.
&nbsp;
Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.
&nbsp;
Best Regards,
Amy
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

marzio · Answer

Hi Amy,&nbsp;thank you for your answer.&nbsp;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.&nbsp;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).&nbsp;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).&nbsp;The output would be something like this with the green line being successful and orange not successful attempts.&nbsp;&nbsp;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&nbsp; &nbsp; &nbsp; (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&nbsp;I hope you can help me.Marzio&nbsp;&nbsp;&nbsp;&nbsp;UPLOADED_DATEFC1_STATUSFC1_UNSUCCESSFUL_STATUSFC1_ATTEMPTED_DATEFC1_CALLBACK_DATEFC2_STATUSFC2_UNSUCCESSFUL_STATUSFC2_ATTEMPTED_DATEFC2_CALLBACK_DATEFC3_STATUSFC3_UNSUCCESSFUL_STATUSFC3_ATTEMPTED_DATEFC3_CALLBACK_DATE&nbsp;SC1_STATUSSC1_UNSUCCESSFUL_STATUSSC1_ATTEMPTED_DATESC1_CALLBACK_DATESC2_STATUSSC2_UNSUCCESSFUL_STATUSSC2_ATTEMPTED_DATESC2_CALLBACK_DATESC3_STATUSSC3_UNSUCCESSFUL_STATUSSC3_ATTEMPTED_DATESC3_CALLBACK_DATESC4_STATUSSC4_UNSUCCESSFUL_STATUSSC4_ATTEMPTED_BYSC4_ATTEMPTED_DATESC4_CALLBACK_DATESC4_COMMENTSSC5_STATUSSC5_UNSUCCESSFUL_STATUSSC5_ATTEMPTED_BYSC5_ATTEMPTED_DATESC5_CALLBACK_DATESC5_COMMENTSSC6_STATUSSC6_UNSUCCESSFUL_STATUSSC6_ATTEMPTED_BYSC6_ATTEMPTED_DATESC6_CALLBACK_DATESC6_COMMENTSSC7_STATUSSC7_UNSUCCESSFUL_STATUSSC7_ATTEMPTED_BYSC7_ATTEMPTED_DATESC7_CALLBACK_DATESC7_COMMENTSSC8_STATUSSC8_UNSUCCESSFUL_STATUSSC8_ATTEMPTED_BYSC8_ATTEMPTED_DATESC8_CALLBACK_DATESC8_COMMENTSSC9_STATUSSC9_UNSUCCESSFUL_STATUSSC9_ATTEMPTED_BYSC9_ATTEMPTED_DATESC9_CALLBACK_DATESC9_COMMENTSSC10_STATUSSC10_UNSUCCESSFUL_STATUSSC10_ATTEMPTED_BYSC10_ATTEMPTED_DATESC10_CALLBACK_DATESC10_COMMENTSA557219707-08-19 01:06:00Successful&nbsp;8-7-2019 9:08&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A541857309-07-19 03:30:00Successful&nbsp;7-9-2019 9:59&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A541857409-07-19 03:30:00Successful&nbsp;7-9-2019 9:57&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A541857509-07-19 03:30:00Successful&nbsp;7-9-2019 9:57&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A555372802-08-19 05:53:00Successful&nbsp;8-2-2019 10:40&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Successful&nbsp;8-5-2019 14:43&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A555372602-08-19 05:53:00UnSuccessfulRinging8-2-2019 11:27&nbsp;UnSuccessfulRinging8-2-2019 12:59&nbsp;UnSuccessfulRinging8-5-2019 9:56&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A551914126-07-19 05:52:00UnSuccessfulCallback7-26-2019 10:017-26-2019 11:01UnSuccessfulRinging7-26-2019 11:02&nbsp;Successful&nbsp;7-26-2019 12:09&nbsp;&nbsp;UnSuccessfulVoicemail8-1-2019 12:18&nbsp;UnSuccessfulCallback8-1-2019 14:058-5-2019 10:00UnSuccessfulNo call for this time8-7-2019 11:05&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

v-xicai · Answer

Hi marzio&nbsp;,
&nbsp;
You may need to unpivot the same call type , call timestamp and call outcome into a separate columns.
&nbsp;
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 &amp; Apply" button. Then you can create relationship between the source table and the data to time intervals table on date field.
&nbsp;

&nbsp;
&nbsp;
&nbsp;
Best Regards,
Amy
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

marzio · Answer

Hi Amy,&nbsp;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.&nbsp;I have tried to SUMMARIZE the columns I need into a separate table but I cannot UNPIVOT that because it is a DAX function.&nbsp;The step I am missing to make it work is creating a new table in Power Query based on a selection of columns.&nbsp;So I can UNPIVOT the columns and follow the rest of your suggestion.&nbsp;Any ideas for that step?&nbsp;&nbsp;I got lost in other threads without finding a working solution.&nbsp;Marzio

Forum Discussion

Visualising best time to call

4 Replies

Related content

Power BI Python Visualisations being depreciated?

Serial No. in visualisation

Visualisation during period of time

Time vs Time Scatter Chart

Dynamic Column Header in Visualisation