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

⚪️

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.

Tagged:

• Indiana 🥷

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`))
```

**Did this solve your problem? Accept it as a solution!**
• ⚪️
edited November 2021

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

• Portland, Oregon 🥷

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.

**Make sure to any users posts that helped you.
• ⚪️

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

• Indiana 🥷

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