First step is I want to get Average Sales By Hour From yesterday to 7 days ago
Second Step, Average Values are like (12 AM -3 , 1 AM - 5 , 2 AM - 5,-------etc)
so cumulative values in graph, 12 - 3 , 1-8 , 2 -13 by hourly
third -Line graph in Average Cumulative Values by Hour Vs Current Day Cumulative Value By Hour
Hello @ThawZin, I am not sure I entirely understand how you are wanting to group the hours in the x-axis. Each grouping has overlapping times with other groupings, so that will make this a lot more complicated to create. I at least feel like we could figure out a way to create an average sales per hour for the previous 7 days though. That would look something like this:
avgOver(
sumOver(
ifelse(truncDate("DD", {Date}) >= truncDate("DD", addDateTime(-7, "DD", now())) AND
truncDate("DD", {Date}) < truncDate("DD", now()), {Sales}, NULL),
[truncDate("HH", {Date})], PRE_AGG),
[], PRE_AGG)
Basically, we we get the average hourly sales by creating sumOver function that will return the sales by hour if the date is between yesterday and 7 days prior. Then, we can put that into an avgOver function to retrieve the hourly average in during that period.
Then, you can create the hourly sales for the current day with this field:
sumOver(ifelse(truncDate("DD", now()) = truncDate("DD", {Date}), {Sales}, NULL), [truncDate("HH", {Date})], PRE_AGG)
That should handle the 2 different values you are wanting to see here, but I am going to need some more information about the Hour groupings to try and figure out how we could display that. It would probably be easier to find a solution if we could just show the hours where each hour is it’s own value.
Thank for your solutions.
Check my requirement again.
This two line are cumulative sales by hours.
June 2 is current day (now)
June 1 is previous . but instead of only June 1 cumulative sales , i want average cumulative value by hour for 7 days.
my formula is runningSum(sum(status), [{Convert Time} ASC], [createdAt]).
convert time : extract(‘HH’,createdAt)
createdAt : Datetime
@DylanM let me hear your feedback please.
Hello @ThawZin, I went through and did some testing on this and I think I have found a solution. I unfortunately don’t have a good dataset to display this for a 24 hour day, so I instead built it for the 7 days in a week. You can convert these calculations to show hour by hour instead of day by day, but I think this will provide a solution for this visual.
You can view the visual and calculated fields I created in this Arena Analysis:
Compare 2 Date Periods
Let me know if you have any questions about the calculated fields. Thank you!
@DylanM , here is sample dataset for my request. I hope it is okay for you. Pleaes help me more.
| Last 7 Days | ||||||||
|---|---|---|---|---|---|---|---|---|
| Hour | Day 1 | Day 2 | Day 3 | Day 4 | Day 5 | Day 6 | Day 7 | Today |
| 0 | 3 | 1 | 2 | 3 | 3 | 6 | 3 | 3 |
| 1 | 2 | 6 | 0 | 1 | 2 | 7 | 2 | 1 |
| 2 | 1 | 7 | 12 | 2 | 1 | 8 | 1 | 2 |
| 3 | 6 | 8 | 3 | 8 | 6 | 3 | 6 | 3 |
| 4 | 7 | 8 | 8 | 3 | 7 | 2 | 7 | 3 |
| 5 | 8 | 3 | 3 | 3 | 8 | 1 | 8 | 6 |
| 6 | 3 | 2 | 2 | 1 | 3 | 6 | 3 | 3 |
| 7 | 2 | 8 | 10 | 7 | 2 | 8 | 2 | 8 |
| 8 | 1 | 3 | 11 | 12 | 1 | 8 | 1 | 3 |
| 9 | 6 | 2 | 12 | 2 | 6 | 3 | 8 | 3 |
| 10 | 7 | 0 | 6 | 1 | 8 | 2 | 3 | 3 |
| 11 | 8 | 12 | 7 | 6 | 3 | 10 | 2 | 8 |
| 12 | 3 | 3 | 8 | 7 | 2 | 11 | 1 | 1 |
| 13 | 2 | 6 | 3 | 7 | 10 | 12 | 6 | 8 |
| 14 | 1 | 7 | 8 | 0 | 11 | 6 | 8 | 3 |
| 15 | 6 | 8 | 3 | 6 | 12 | 7 | 12 | 1 |
| 16 | 7 | 3 | 2 | 1 | 6 | 8 | 6 | 6 |
| 17 | 8 | 2 | 1 | 3 | 7 | 2 | 7 | 3 |
| 18 | 3 | 1 | 6 | 3 | 8 | 1 | 8 | 8 |
| 19 | 2 | 6 | 8 | 8 | 3 | 6 | 3 | 1 |
| 20 | 1 | 1 | 3 | 7 | 7 | 1 | 7 | 8 |
| 21 | 6 | 6 | 8 | 3 | 3 | 6 | 3 | 3 |
| 22 | 7 | 7 | 7 | 8 | 8 | 7 | 8 | 6 |
| 23 | 8 | 8 | 8 | 7 | 7 | 8 | 7 | 3 |
@DylanM , I got the solution like
Current Day Cumulative Sales By Hour : runningSum(sumOver(sum(ifelse(truncDate(“DD”, now()) = truncDate(“DD”, createdAt), status, NULL)), [{Convert Time}]), [{Convert Time} ASC], )
Last 7 Day Average Cumulative Sales By Hour : runningSum(
avgOver(
sumIf(
status,
truncDate(“DD”, createdAt) >= addDateTime(-7, “DD”, truncDate(“DD”, now())) AND
truncDate(“DD”, createdAt) <= addDateTime(-1, “DD”, truncDate(“DD”, now()))
),
[{Convert Time}], POST_AGG_FILTER
),
[{Convert Time} ASC]
)
I am not sure , not showing average , show working as sum by hour
My Old Version:
Next Version : check last 7 days average
Hello @ThawZin, I am not sure it matters here, but I would use an ifelse function instead of a sumIf. I just do not particularly like how those functions work and sometimes they do not play well with the LAC-W aggregations like avgOver.
Also, I am wondering if you add the empty partition brackets at the end of your runningSum, if that would resolve the issue:
runningSum(
avgOver(
sum(
ifelse(
truncDate(“DD”, createdAt) >= addDateTime(-7, “DD”, truncDate(“DD”, now())) AND
truncDate(“DD”, createdAt) <= addDateTime(-1, “DD”, truncDate(“DD”, now())),
status,
NULL
),
[{Convert Time}], POST_AGG_FILTER
),
[{Convert Time} ASC],
[]
)
Also, are there any other filters that you are applying to the visual that could be causing the last 7 days values to be reduced? Make sure you are not using a date filter on the visual that could disrupt the results.
@DylanM , great , thanks. Got it now . Cus I did filter on visual
but my issue is like even though current time is at 7 AM , but trend is showing at other time that’s why trend is flat , how to avoid it.
Current Formula is runningSum(sumOver(sum(ifelse(truncDate(“DD”, now()) = truncDate(“DD”, createdAt) AND
{Convert Time} <= extract(‘HH’, now()), status, NULL)), [{Convert Time}]), [{Convert Time} ASC], )
Last 7 days average cumulative is runningSum(
avg(sumOver(
ifelse(
truncDate(‘DD’, createdAt) >= addDateTime(-7, ‘DD’, truncDate(‘DD’, now())) AND
truncDate(‘DD’, createdAt) <= addDateTime(-1, ‘DD’, truncDate(‘DD’, now())),
status,
NULL
),
[{Convert Time}],
PRE_AGG
)
/ 7),
[{Convert Time} ASC]
)
Hi @DylanM , let me hear your feedback.
Hi @DylanM , May I know your feedback
Hello @ThawZin
Are you still working on this or were you able to find a solution?
I think what’s happening here is runningSum for Now is reacting to the x-axis in your visual so if the total is 31 for the current time, its is going to pass that value to all other times because that is the “total” essentially.
You could try adding an extra logic step in your calculation that would act as a cut off. So adding logic like this:
ifelse(extract('HH', {Convert Time}) <= extract('HH', now()), runningSum(sumOver(sum(ifelse(truncDate(“DD”, now()) = truncDate(“DD”, createdAt) AND {Convert Time} <= extract(‘HH’, now()), status, NULL)), [{Convert Time}]), [{Convert Time} ASC],[ ]) , NULL)
My gut tells me this could throw an error but let me know if that works!
@duncan , still showing mismatch error. because
convert time is extract from created at , already hour .therefore I replaced created at in the syntax still showing error , please let me know the update
1 Like
Hey @ThawZin
I think this would be easier to troubleshoot in arena. Would you be able to recreate this there with sample data so we can work through this easier?:
1 Like
Hi @ThawZin,
It’s been awhile since we last heard from you on this thread, are you able to upload a sample copy of your dataset/analysis to the Arena mode so that we can try and better assist with your case?
If we do not hear back within the next 3 business days, I’ll close out this topic.
Thank you
Hi @ThawZin,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.
Thank you