Getting Previous Year Actual Sales to Populate

FreshSqueezed

Hey all-

I'm sure this will come off relatively simple but this one has me stumped.

We switched ERP systems and I'm trying to get previous year sales to populate in a bar/line chart that shows current sales (by month in the bars) and previous year sales (by month in the line). I cannot get the new ERP system data to load for previous year based on my formula. However, when I set the date range to the previous year Domo is retrieving the data in the bars.

I thought this would be straight forward, but here's what I'm using:

case when `Lag Date` = `Exec date` then `Invoiced Amount` else 0 END

Lag Date = Exec Date - 12 Months using the Date Operations function

Exec Date = Invoiced Date

Invoiced Amount = $ Sales

I've also tried using date functions such as case when year (exec date) = year (exec date)-1 then invoiced amount... but that didn't populate either. Any advice?

Thank you!

MarkSnodgrass

Here is what you can do assuming you are using the Line + Stacked Bar card.

Create another beast mode called PreviousYearSales that looks like this in and put it in the Y-axis:

(CASE when YEAR(`execdate`) = YEAR(CURRENT_DATE())-1 THEN `invoiceamount` ELSE 0 END)

Create a beast mode called CurrentYearSales that looks like this and put it in the series:

(CASE when YEAR(`execdate`) = YEAR(CURRENT_DATE()) THEN `invoiceamount` ELSE 0 END)

Create another beast mode called MonthName and put it in the x-axis:

MONTHNAME(`execdate`)

Create another beast mode called MonthNumber and put in the sorting properties and sort ascending with no aggregation:

MONTH(`execdate`)

Set your date range filter to the last 12 months

This should get you what you are looking for if I am understanding your data correctly.

FreshSqueezed

No dice. Now it flat lines all previous year sales. Although I thought that would at least work for the PreviousYearSales

Not sure if this means anything without understanding the ETL dynamics, but...

Jan-Apr is old ERP system 1

May-Dec is New ERP system 2

I'm trying to append Old ERP system with New ERP system via Previous Year Actual.

I have a Check Card to see if I can even get the Previous Year data from New ERP system to even populate the data but that won't work either. Rambling, but short story long, still not able to get the new ERP to pull LY data.

mhouston

@FreshSqueezed in your ETL are you appending the old ERP dataset to the new ERP dataset? Are your column names from your old ERP Dataset the same as your new ERP Dataset?

I did something similar when we migrated our BD/marketing system, and in my ETL used a select columns on our old dataset to rename the columns to align to the new CRM dataset column names.

FreshSqueezed

Hi @mhouston yes, I'm appending new to old. The column names are identical.

2 things:

1 If I isolate the new ERP and try to look at PY Actuals before the append I still don't get the data, therefore;

2 The append is actually not a problem (yet as far as I know). But the new and old ERP are cooperating for this year actuals.

mhouston

@FreshSqueezed looking at your data check card it looks like you have it filtered to this year by month. Is your original card filtered the same way?

If so - I think that's your problem because you're filtering your dataset to only rows in that time frame i.e. you have no rows in your dataset for previous year.

FreshSqueezed

hey @mhouston

So, I was using that as a sample pic to show that even my test isn't working.

Better said with my previous year calculation/beast mode ((CASE when YEAR(`Lag Date`) = YEAR(CURRENT_DATE()) THEN `Invoiced Amount` ELSE 0 END)) i'm not getting any data.

However, domo is reading the data as it should be, ie pulling in this year sales (invoiced amount) and when i set the date filter to Last year, it's still grabbing last year's data. So ultimately, the question is, if I have the test card filtered to This Year, like the first picture, how do I get the Previous Year Actual to populate?

Lag Date = Exec Date - 12 Months using the Date Operations function

Exec Date = Invoiced Date

Invoiced Amount = $ Sales

mhouston

@FreshSqueezed I'm making the assumption that your date filer is on 'Exec Date' field. If so, you can't set the date range filter on the card to this year and still get previous year actuals. You will need to remove the data filter (or broaden it to include this year and last year).

I think Mark's original solution is on point minus the date range filter - in his solution you shouldn't even need to do a lag date calculation in your ETL.

mhouston

as far as your beast mode with lag date not returning - if I'm understanding your explanation correctly, the max possible value in your lag date field would be today's date minus 12 months (assuming your exec date cannot be greater than today's date) - in which case you will never have year(lag date) = year(current date), and your beast mode will always return zero.

FreshSqueezed

@mhouston OK, interesting. So, I'm going to reply to your last comment because ultimately even having New ERP isolated my Previous Year Actual is still populating a 0. I think once I get that fixed I can handle the other stuff.

So:

Lag Date: Exec Date - 12 Months

So should my formula be (CASE when YEAR(`Lag Date`) = YEAR(CURRENT_DATE()-1) THEN `Invoiced Amount` ELSE 0 END)?

If I try and do this methodically, will this be the solution to get me "Last Year Actual" to view in the same card as This Year Actual (which is just Invoiced Amount).

mhouston

Yes, I would expect that to work.

FreshSqueezed

@mhouston got a little movement, but not the right way.

So, if I do that: (CASE when YEAR(`Lag Date`) = YEAR(CURRENT_DATE())-1 THEN `Invoiced Amount` ELSE 0 END)

then, I'm getting the Invoiced Amount for This Year. It's not pointing to Last Year.

mhouston

@FreshSqueezed re-reading, that makes sense.

Since you are doing your lag date in ETL, I am assuming your data looks like this:

So what you want is for last year actuals to return the 150 from the second row, but your calculation is going to return the 100 from row one because lag date = 1/27/2021.

I would either write your beast mode as  (CASE when YEAR(`Exec Date`) = YEAR(CURRENT_DATE())-1 THEN `Invoiced Amount` ELSE 0 END)

or change your existing beastmode to  (CASE when YEAR(`Lag Date`) = YEAR(CURRENT_DATE())-2 THEN `Invoiced Amount` ELSE 0 END).

FreshSqueezed

Yea, so I think I mistyped it now that I reread it myself.

I do want your first row where the 100 should pertain to 1/27/2021.

I showed you a formula that would have pertained to the previous day, not year. I just tried your second example, and no luck.

So, to summarize, because the second beastmode doesn't work either.

(CASE when YEAR(`Lag Date`) = YEAR(CURRENT_DATE())-1 THEN `Invoiced Amount` ELSE 0 END) is populating January 2022 sales (this year). Which is an inch more than I had before because I wasn't getting any data.

 (CASE when YEAR(`Exec Date`) = YEAR(CURRENT_DATE())-1 THEN `Invoiced Amount` ELSE 0 END) is not getting me any data for any month of 2021

also

(CASE when YEAR(`Lag Date`) = YEAR(CURRENT_DATE())-2 THEN `Invoiced Amount` ELSE 0 END) provides no data as well.

I appreciate your time and support. I'm baffled.

MarkSnodgrass

@FreshSqueezed if you can provide a couple rows of data from the dataset that you are building the card with that would help @mhouston and I a better understanding of what might be going wrong with the formulas that you have tried.

mhouston

There must be something else in your card configuration that's filtering out the data. I just loaded that dummy dataset into my own instance and created the beast modes and it's showing data:

FreshSqueezed

@mhouston & @MarkSnodgrass

So, we didn't implement our new erp until May, and that data forward isn't working.

So, if we take the top August example, obviously Invoiced amount for 2022 will be $0 (well maybe not obvious, but it's zero). I would expect Last Year's Amount to be $70,560.

@mhouston I'm going to look at what you just provided.

FreshSqueezed

I think what's happening is that my cards don't have Exec Dates for May 2022 - XX/XX/XXXX. The PY actual should be zero in our new ERP because we didn't implement it until May 2021. I can't use this formula until we're at the month of the exec date currently, ie May. We don't have any future invoiced orders.

I'm going to feel like a real idiot if this is the case. Because all of these formulas should have worked.

mhouston

@FreshSqueezed I plugged the first 10 rows of your sample data into that same demo card config and those last year formulas do work:

can you share a screenshot of your card config?

FreshSqueezed

That's what I'm saying. Is that Last Year's Actuals exist in the Exec Date format. But not when i put it into a calc for Previous Year Actual. I"m so baffled and frustrated

mhouston

Thanks for adding this!

It looks like your chart has the date range filtered to this year - so the beast mode I gave you is not going to work unless you remove that date filter.

It looks like the current configuration has no beast modes - so your ETL is calculating the previous year actual? Or it is coming in from the ERP dataset?

I think you either have to 1) modify the ETL to correct the calculation of previous year actual or 2) change your card configuration to remove the date filter, and create a beast mode for each of the measures you are currently using in your y-axis and series fields.

FreshSqueezed

Correct, I created a formula in the ETL to capture this information.

It's weird because the old ERP data (jan-apr) is working.

Thank you for all your help. I can try the beast mode in the card I suppose?

mhouston

@FreshSqueezed you would have to use different logic in an ETL because the formula only executes on that row. I think you'd have to join the data to itself on lag date = exec date so that you'd have a second column for lag inv amount... beast mode is definitely going to give you more flexibility. @GrantSmith has a good write up that has more detail: https://dojo.domo.com/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest

FreshSqueezed

I got it to work the cheap way.

I created an excel spreadsheet with PY Actuals but used Exec date as 2022. I have no idea what this means. But, basically I need Domo to take Last Year's Invoiced amounts but apply This Year's date.

Better Said.

The amount invoiced on 12/1/2021 = $100

Written formula of 12/2/2022 = $100 (even though it really occurred in 2021)