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

DANIH
DANIH Member
in Magic ETL

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:

dojo screen shot hourly.PNG


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:

Answers

  • GrantSmith
    GrantSmith Coach

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


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • DANIH
    DANIH Member
    edited November 2021

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

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • DANIH
    DANIH Member

    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
    GrantSmith Coach

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • DANIH
    DANIH Member
    edited November 2021
    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

Categories