Creating Table Chart with Sales & Sales Last Year

patrickp

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.

ColemenWilson

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.

GrantSmith

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:

https://community-forums.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons

patrickp

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 below