Quicksight Accumulative

Question & Answer
,
1

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

image
image800×261 27.4 KB

2

Hi @ThawZin ,

your header says you want to cummulate the new Sales, I cannot find a sales measure in your formula. Do I miss something?

In case you do need to adjust the time zone, you can do so:

image
image482×758 44.6 KB

Best regards,
Nico

If this helped you, please mark my answer as solution. That helpes the community to find solutions faster.

3

Yes ,
For example, Current Cumulative time is at 13 PM , but in the graph line showing ahead another time constantly. Want to see in like that

image
image1375×445 36.4 KB

see blue line

4

@Nico , could you please check it ? even I’ve changed correct time zone , graph line showing like first photo , second photo is from another example not from cumulative

5

Hi @ThawZin ,

first idea is to use a filter in your formula like if hour from now is smaller than your timestamp, then NULL else your formula.

Which of the formulas mentioned earlier is the formula for “Now”?

Is it possible for you to use QucikSight Arena? Then the data (maybe an example set) and formulas are availalbe for me in a sandbox.

Best regards,
Nico

6

Hi @ThawZin

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!

7

Hi @ThawZin

Since we have not heard back from you, I’ll go ahead and close/archive this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.

Thank you!