Daily / Hourly Phone Reports

jman0527

Hello, we have 6 report logs of phone usage with a count of incoming calls sorted by hour along with a total calls daily. This report is coming in daily and I'm attempting to create a historical for it, the historical is updating but we are having 2 issues. Firstly, Domo is displaying the information in an odd order, randomly shuffling the times and dates around so the data is difficult to read. Secondly we are getting excess data from some reports, most of this data can be removed with the remove duplicates function, but the hour the report comes across (assuming we get more calls that hour) and the total column won't be exact duplicates but are still extra data from earlier in the day. Any help would be much appreciated, Thanks!

Darius

Thank you, jman0527. If you need to keep one of the total rows, but exclude the other one it may require expanding your dataflow. Here is an approach you could take, however, to address your goal, which involves addressing your total rows separately from the rest of your data:

1. Filter for rows that contain "Total" so you only have those rows.

2. Use the "Group By" column to take the max value (you could do this by date as well if needed) of your total

3. Separately, filter for rows that do not contain totals

4. Append the result of step 2 to the result of step 3 using the "Append Rows" tile

Darius

Hello jman0527,

 

Thank you for posing your question about creating a historical dataflow for phone call data. I hope to address the issues you are seeing. Where are you seeing an odd order for your data? If this is in a card, you can add a sort to order the data how you want to see it. This would apply to the visualization, even if the data is being displayed in a table.

 

Excess data could come from the input datasets bringing in duplicate data (which could be addressed by appending only new data), two of your 6 report logs having the same data, or a join that is duplicating your data. If there is more I can review as I address your question, please let me know.

jman0527

Hi, Would we be able to just sort on both time and date then? On the duplicate data all 6 data sets are different, each getting its own historical and own daily report, the issue is removing duplicates because if we export one report halfway through the day the totals row won't match later reports, I've considered pulling the report hourly and removing all rows that are zeroed out (no report data for the day yet) but the issue of the total column persists, thanks!

Darius

Thank you jman0527. In the card you could sort on your date and time values to order the data, as you mentioned. I may need some additional information about the total row not matching between reports, but something that you can consider is having two outputs from your recursive dataflow: one that is filtered and used for reports and another that maintains the recursive aspect of your dataflow to keep historical data. Please let me know if that does not address your question with some additional context for the discrepancy you are seeing with total rows. Thank you!

jman0527

Thanks again for the help, I'm including photos to help explain, as you can see in this example we have all our incoming reports and some of the historicals, looking at one of them (flow attached) we removed duplicates, but the totals column isn't removed, because it's not a duplicate. This column needs to be removed because it is from earlier in the day so we are essentially seeing the 5th one and a half times because the report published multiple times, I hope that makes more sense and thanks again!

Darius

Hello jman0527,

 

I believe the "Filter Rows" tile would address your goal of removing the totals better than the "Remove duplicates" tile. Removing duplicates removes rows of data where all of the fields specified have the same combination of values, however, you can filter out (or for) specific values. In your use case you can create a filter to not contain rows where your column contains the value "Total". The following article can be a good point of reference for you as you work on filtering your data in your dataflow:

 

https://knowledge.domo.com/?cid=etlactionseditdata

 

Please let me know if the "Filter Rows" tile does not address your goal so I can take another look. Thank you!

jman0527

I'd considered this, but ideally we'd like to keep the correct totals column if possible, it will always be the larger value with a later report time, Thanks again for all the help!

Darius

Thank you, jman0527. If you need to keep one of the total rows, but exclude the other one it may require expanding your dataflow. Here is an approach you could take, however, to address your goal, which involves addressing your total rows separately from the rest of your data:

1. Filter for rows that contain "Total" so you only have those rows.

2. Use the "Group By" column to take the max value (you could do this by date as well if needed) of your total

3. Separately, filter for rows that do not contain totals

4. Append the result of step 2 to the result of step 3 using the "Append Rows" tile

jman0527

Awesome! Thanks again for all the help!