Swap data using table calculations

Recently, one of our customers who is leveraging risk insights for log ingestion ran into a problem. For certain dates of the month (April 14 & 15), the ingestion swapped "Bytes Uploaded" and "Bytes Downloaded." How can they leverage Advanced Analytics to swap the data back so that their monthly reporting would be accurate? Here is one solution with Table Calculations. 

The first step is to create a Custom Dimension with command:

concat(${page_event.event_timestamp_date},"") 

This converts the format of all “Event Dates” from “Timestamp” to “String,” which helps avoid potential errors in our Table Calculations. We name the dimension Event Date - String and then save it.

The second step is to create Table Calculations which swap the data for particular dates (April 14 & 15 in this case). Let’s start with the Table Calculation swapping “Bytes Uploaded” to “Bytes Downloaded.”

We create a Table Calculation with command:

if(

  ${event_date_string}="2024-04-14"

  OR

  ${event_date_string}="2024-04-15",

  ${page_event.sum_bytes_uploaded},

  ${page_event.sum_bytes_downloaded}

)

This swaps “Bytes Uploaded” to “Bytes Downloaded” for April 14 & 15, 2024.

We name the calculation Swapped - Bytes Downloaded and then save it. By applying the calculation to the visual, we can see that “Bytes Uploaded” has been swapped to “Bytes Downloaded” for April 14 & 25 only. The data for other dates is not swapped.

We now create another Table Calculation with command:

if(

  ${event_date_string}="2024-04-14"

  OR

  ${event_date_string}="2024-04-15",

  ${page_event.sum_bytes_downloaded},

  ${page_event.sum_bytes_uploaded}

)

This swaps “Bytes Downloaded” to “Bytes Uploaded” for April 14 & 15, 2024.

We name the calculation Swapped - Bytes Uploaded and then save it. By applying the calculation, we can see that “Bytes Downloaded” has been swapped to “Bytes Uploaded” for April 14 & 25 only. The data for other dates is not swapped.

You can also further customize the Table Calculations by changing the dates based on your use cases. The only thing you need to change is the command:

${event_date_string}="2024-04-14"

OR

${event_date_string}="2024-04-15",

You can duplicate the command and add any additional dates. For example:

${event_date_string}="2024-04-14"

OR

${event_date_string}="2024-04-15"

OR

${event_date_string}="2024-04-16"

OR

${event_date_string}="2024-04-17",

By doing so, the Table Calculations will then swap the data for all additional dates added.

The visual template is attached below. Feel free to import and view it in your own environment. Let us know if you have any questions!