Need Advice on Approach: Producing "... to date" comparisons when data granularity is mixed

swagner

Ok... this is a mind bender for me, and maybe that's because I'm too close to it... (forest for the trees).  Hoping the Dojo can come to the rescue and point me in the right direction.

 

Here's the business question:  How can I see how our sales are performing to plan and vs prior year?  (YTD, MTD - and including vs the same day last year)

 

The data:  

• I have raw (uncorrected) sales transactional data with daily granulatiry  (current year, prior year +)
• I have official financial sales data from accounting (any issues corrected) with month granulatity (plan & actual)

When to use which data:  Once a month is closed by accounting we want to use the official financial data, broken down to a weekly grain.  I am doing this now by dividing the total monthly values by the weeks in the accounting period (5-4-4).  Thinking I need to next break it down to daily grain?  If the month is not yet closed by accounting we want to use the raw sales data.  I have a comparison in my model now that checks on which period is closed and brings in the data from each source as I described.  That part is fairly straight forward.

 

I currently have a page and cards that are showing our performance "This Month" and "This Year", but when we start a new period the comparison is skewed because the history is a full weeks worth of data vs maybe only a couple of days.  The gap closes as the days progress in the data.  For example:  today there is data through Thursday's invoicing compared to a full week of plan/prior year data.  Saturday morning the data is apples to apples from a number of days standpoint.  Our leadership wants to see comparable values without that skewed view... hence the need for daily granularity.  

 

Am I making sense?

 

@jaeW_at_Onyx maybe a topic for a video?

jaeW_at_Onyx

piece of cake.

 

You need two chunks of data.  "Sales Actual by day" and "Financial allocated by day."

Yes, you need to break your financial data into one row per day.  Decide if you have 20 or 30 days per month.

 

APPEND the two together and differentiate by 'Activity Type' or some similar column.

 

Then decide how you're going to handle reporting MTD , YTD, and Same Day Last Year across your entire dataset (in a way that consistently makes sense). Of special consideration will be how you handle weekends and holidays.

 

You can use my 'Parameterized Query method https://www.youtube.com/watch?v=wmMrnPO9ivY&t=1s to dynamically set reporting dates.

 

OR you can build a date dimension that calculate one row per day per period type.  where period type is MTD or QTD or PM or PY.    https://www.youtube.com/watch?v=CDKNOmKClms&t=223s

 

---

@swagner wrote:

Here's the business question:  How can I see how our sales are performing to plan and vs prior year?  (YTD, MTD - and including vs the same day last year)

 

The data:  

• I have raw (uncorrected) sales transactional data with daily granulatiry  (current year, prior year +) -- DON'T DO THIS IN ETL.  USE A FUSION + A WEBFORM OR DIMENSION TABLE
• I have official financial sales data from accounting (any issues corrected) with month granulatity (plan & actual)  -- ALLOCATE THIS TO THE DAY LEVEL

When to use which data:  Once a month is closed by accounting we want to use the official financial data.   -- Build an isClosed FLAG into your dataset, so you can compare raw vs. financial data

If the month is not yet closed by accounting we want to use the raw sales data.

 

leadership wants to see comparable values

 

 

jaeW_at_Onyx

piece of cake.

 

You need two chunks of data.  "Sales Actual by day" and "Financial allocated by day."

Yes, you need to break your financial data into one row per day.  Decide if you have 20 or 30 days per month.

 

APPEND the two together and differentiate by 'Activity Type' or some similar column.

 

Then decide how you're going to handle reporting MTD , YTD, and Same Day Last Year across your entire dataset (in a way that consistently makes sense). Of special consideration will be how you handle weekends and holidays.

 

You can use my 'Parameterized Query method https://www.youtube.com/watch?v=wmMrnPO9ivY&t=1s to dynamically set reporting dates.

 

OR you can build a date dimension that calculate one row per day per period type.  where period type is MTD or QTD or PM or PY.    https://www.youtube.com/watch?v=CDKNOmKClms&t=223s

 

---

@swagner wrote:

Here's the business question:  How can I see how our sales are performing to plan and vs prior year?  (YTD, MTD - and including vs the same day last year)

 

The data:  

• I have raw (uncorrected) sales transactional data with daily granulatiry  (current year, prior year +) -- DON'T DO THIS IN ETL.  USE A FUSION + A WEBFORM OR DIMENSION TABLE
• I have official financial sales data from accounting (any issues corrected) with month granulatity (plan & actual)  -- ALLOCATE THIS TO THE DAY LEVEL

When to use which data:  Once a month is closed by accounting we want to use the official financial data.   -- Build an isClosed FLAG into your dataset, so you can compare raw vs. financial data

If the month is not yet closed by accounting we want to use the raw sales data.

 

leadership wants to see comparable values

 

 

German_Rodriguez

Thank you i was able to solve similar issue thanks to https://youtu.be/CDKNOmKClms