Creating Table Chart with Sales & Sales Last Year
I have a dataset where each dow represents a daily transaction 'date' for the 'productSKU', 'size', 'sales channe'l and also contains other columna where it sums the 'sales', 'stock on hand', 'units sold', etc
I am trying to create a calculated field for 'sales last year' as a column for the 'sales' from a year ago for the matching 'transaction date'.
The layout for the car is a transpose table, where I have the 'DAY' of the week where each row as the 'sales', 'Stock On hand', "unit sold'.
we can apply the filters on the card such as 'sales channel' and date range and need to be able to add this new column where it will show that row with last year's data.
I have created a column called 'date last year' and having trouble calculating the 'sales last year' from a 'date' match.
Answers
-
The problem you are going to run into is that when you select a date range as a filter, it will filter out the rows from the previous year because those rows are not within the date range you are selecting - making it impossible to compare to their values because those values have been filtered out. One solution would be to bring the previous year up to the current year by joining the previous year value to the current year. For example, your data would start by looking like this:
And end by looking like this:
One flaw in this is that it sounds like you are looking at day of week and not the actual date, so a monday this year might be a sunday last year. You could solve this by joining on day of week and week number instead of date.You might also explore using period over period chart types.
If I solved your problem, please select "yes" above
0 -
To avoid the issue of comparing a Monday this year to a Sunday last year, for example, instead of subtracting a year from your date, you can subtract 364 days.
I've done a write-up about restructuring your data for period over period type analysis that outlines what @ColemenWilson is talking about. You can read more about it here:
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Hello and thank you for answering my post.
I was trying to implement your approach as its a good point you raised about the day of the week.
I added the following formulas where I would get the matching date from last year.I grouped it by YearWeekDay and Sales Channel as I am trying to get the sales from last year's date & sales channel as added to the 'Sales LY' column, however, its showing as Null although there is is YearWeekDay value from last year in the dataset for the same sales channel.
This is how I have grouped the data.
It is then joined back to the dataset as below
Once I have this working, I would be able to add it to my card as another row for Sales LY in the sample table below0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 298 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 680 Automate
- 175 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 33 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive