I would like to calculate a running total previous day same hour:

[ThreeEleven]

As an example:

VisitorTimeStamp visitors. run total today. Prev Day Same hour. Prev Day Running total
8/12/2024 12 am 10. 10 8 8
8/12/2024 1 am. 20. 30. 25. 33
…
I have tried this: I get 0’s everywhere
Previous day =
ifelse(
{visitorTimeStamp} >= truncDate(“HH”, addDateTime(-1, ‘DD’, {VisitorTimeStamp}))
and {visitorTimeStamp} < addDateTime(1, ‘HH’, truncDate(“HH”, addDateTime(-1, ‘DD’, {visitorTimeStamp}))),
visitorCount,
0
)

runningSum(sum({Previous Day}), [truncDate(“HH”, {visitorTimeStamp}) ASC])

[Brett]

Hi @ThreeEleven and welcome to the QuickSight community!
One thing I noticed above was inconsistent use of the quotation marks. An updated calculation may look something like:

ifelse(
    {visitorTimeStamp} >= truncDate('HH', addDateTime(-1, 'DD', {visitorTimeStamp}))
    and {visitorTimeStamp} < addDateTime(1, 'HH', truncDate('HH', addDateTime(-1, 'DD', {visitorTimeStamp}))),
    {visitorCount},
    0
)

Let me know if this helps or if you have any additional questions!

[ThreeEleven]

@Brett
I got 0 everywhere

[Brett]

Hi @ThreeEleven,
Ok, let’s try this. So you’ll need to create 3 calculated fields.
First, will be a date field that truncates to hour to use in your other calculated field.
Order Date Hour
truncDate('HH', {visitorTimeStamp})

Then,
Hour by Hour

runningSum
(
  sum(visitorCount)
  ,[ {Order Date Hour} ASC]
  ,[]
)

Last,
Hour by Hour Yesterday

lag
(
 {Hour by Hour}
 ,[{visitorTimeStamp} ASC ]
 ,24
 ,[]
)

Then use these calculated fields in your visual and you should receive the output you’re looking for.

Let me know if you have any additional questions or if this solution works for your case.

Thank you

[Brett]

Hi @ThreeEleven,
It’s been awhile since we last heard from you so checking back in to see if you had any additional questions or if the suggestion above worked for your solution?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

[ThreeEleven]

Sorry for late response; Unfortunately, that won’t work. I was able to solve it and I will provide details for the community

What I ended up doing was creating a few of calculated fields:

1. Day = ifelse(
   VisitorTimeStamp >= truncDate(‘DD’, now()), “Today”,
   “Yesterday”
   )

2. LastWeek = ifelse(
   VisitorTimeStamp > truncDate(‘DD’, now()), “Today”,
   truncDate(
   ‘DD’,
   VisitorTimeStamp) = truncDate(‘DD’, addDateTime(-7, ‘DD’, now()))
   AND extract(‘HH’,
   VisitorTimeStamp) -7
   <= extract(‘HH’, now()),“Last Week”,
   NULL
   )

3. I had to extract the Hour from the timestamp:

HourVisit = extract(‘HH’, {Dynamic Period})

Repeat step 2 if you would like LastMonth, Last(Insert Time Period Here)

Then, because I wanted it to be dynamic I created another calculated field to switch between Time Periods:

switch(${TimeIntelligence},

‘Daily’, Day,

‘Weekly’, LastWeek,

‘Monthly’, LastMonth,

‘Yearly’,LastYear,

Day

)

Then for each parameter from above I used this calculated field:
runningSum(
sum(QuoteCount),
[HourVisit ASC],
[DateComparision]),
Then I get the required output by comparing today vs yesterday or Today vs. (whatever Period)