Is this doable? Seeing percent of total day sales by hour using Timestamp column.

DANIH · 2021-11-09T15:48:38+00:00

There was an error rendering this rich post.

Hello!

I have a problem that needs to be visualized and it was phrased to me like this: I need to see what percentage of sales each hour had for the total day last year then I need that percentage multiplied by the daily goal to get an hourly goal for today. So we have this chart right now:

this show us net sales today, yesterday, and LY. The LY isn't working right now just because there's a hole in the data for the next week. Anyways, we want to be able to have a goal or bar that shows goal per hour based on percentage of total day LY if that makes sense.

Now for this data, each row is an item in an order so if a order has 3 items in it then one order has 3 lines. I have a timestamp column that acts as my date column and a Net Sales column already so each line has a total net sales #. Is there a way to do this? If this makes any sense I don't know but I have almost just ripped out my hair trying to figure out window and rank functions or group by functions to figure this out. If anyone can help me walk through this that would be greatly appreciated.

Find more posts tagged with

Date

Grouping

Magic ETL

Accepted answers

All comments

GrantSmith

Hi @DANIH

Have you tried a window function with a case statement since you're attempting to only look at data from today?

Last Year:

SUM(SUM(CASE WHEN DATE(`Timestamp`) = CURRENT_DATE - INTERVAL '1' YEAR THEN `Net Sales` END)) OVER (PARTITION BY HOUR(`Timestamp`))

Last Year (With day of week aligned)

SUM(SUM(CASE WHEN DATE(`Timestamp`) = CURRENT_DATE - INTERVAL '364' DAY THEN `Net Sales` END)) OVER (PARTITION BY HOUR(`Timestamp`))

Yesterday:

SUM(SUM(CASE WHEN DATE(`Timestamp`) = CURRENT_DATE - INTERVAL '1' DAY THEN `Net Sales` END)) OVER (PARTITION BY HOUR(`Timestamp`))

Today:

SUM(SUM(CASE WHEN DATE(`Timestamp`) = CURRENT_DATE THEN `Net Sales` END)) OVER (PARTITION BY HOUR(`Timestamp`))

DANIH

Would these be in my beast mode or formula tile in etl? @GrantSmith

MarkSnodgrass

Whenever I am needing to show a goal, I tend to use the Bullet Chart, so I would suggest looking at that for your visualization.

https://domohelp.domo.com/hc/en-us/articles/360042924154-Bullet-Chart

As for the data, I would start with adding a group by to total your orders by datetime, so that you have one row for each datetime interval you want. I would then add a formula tile that creates a datetime field of one year prior. You can use DATE_SUB to do this. I would then join on this field back to your original dataset so that you have the prior year's value next to the current year value. I would do a left join in case there is no match. You can then do the math to create your goal in another formula tile.

This should lay out the data nicely to easily drop into a bullet chart.

DANIH

I did these in beast mode and it worked but its basically what I had before. I need to be able to take the percentage that each hour had Last year for the total day and times that by today goal column. What's the next step for that? @GrantSmith

GrantSmith

@DANIH Do you have some sample anonymized data I could play with?

DANIH

Anonymized data for DOJO.xlsx

Here is something I just made up. I have a timestamp column with todays net sales for example it only goes through hour to date. Then I have last years net sales. There is a goal column so if we can figure out what each hour percent total for the whole day was for last year then times that by the goal for today for each hour we could get a dollar amount per hour that I need to hit in order to reach the goal for today. Hopefully that makes sense. I don't know if this is possible but hopefully we can figure something out! To better understand I added this Excel to show what I want it to look like. The Date column might be weird but you can fix that I'm guessing.

I want it to look like this DOJO.xlsx

@GrantSmith