Issue with Sales Data Tracking and Date Transformation

MycahD

Hello,

I'm encountering an issue with tracking sales data in from our CRM. The problem arises from the fact that the table card relies on the 'Date Created' column for capturing engagements. Although this successfully captures most sales data, it fails to include engagements that were created in the previous year but completed in the current year.

I would appreciate any input or suggestions on how I can modify my transformation to capture the following:

1.All engagements created in the current year.
2.Engagements that were completed in the current year, regardless of their creation date.

Data columns from this dataset include:

Date Created
Sales Date
Current Date

Thank you for your assistance!

GrantSmith

You'll need to stack / pivot your data so you have a singular date field instead of multiple per row.

You data would look something like:

Order ID | Date | Date Type | Order Total

1 | 1/1/2023 | Created | $123

1 | 1/7/2023 | Sales | $ 123

Then you can use the new Date field to make sure you're getting records affected by each. If this is single line sales data it can get duplicated if it happens in the same year so you'll need to dedupe your data or consider it as part of your calculations. You can use a window function to divide your order amounts so that when the multiple lines get added together it'll add up correctly.

Something like this might work.

SUM(`Order Total` / COUNT(`Order ID`) OVER PARTITION BY `Order ID`)

GrantSmith

You'll need to stack / pivot your data so you have a singular date field instead of multiple per row.

You data would look something like:

Order ID | Date | Date Type | Order Total

1 | 1/1/2023 | Created | $123

1 | 1/7/2023 | Sales | $ 123

Then you can use the new Date field to make sure you're getting records affected by each. If this is single line sales data it can get duplicated if it happens in the same year so you'll need to dedupe your data or consider it as part of your calculations. You can use a window function to divide your order amounts so that when the multiple lines get added together it'll add up correctly.

Something like this might work.

SUM(`Order Total` / COUNT(`Order ID`) OVER PARTITION BY `Order ID`)