How to display a bar chat by date and cutoff time?

Question & Answer
, , , , , , ,
1

Hello,

My bar chart shows the number of different customers per day.
Ex: distinct_countif({id_customer}, {stage} = ‘1’)

image
image820×620 15.7 KB

I need to show the number of distinct customers per day, but by time (cutoff time), that is, if it is 8am, I need to show all the bars from the previous days according to the 8am time, if it is 12pm, I need to show all the bars from previous days until 12pm.

How to do this?

Tks!

2

In this case i suggest creating a calculated field with this time bucket… Quicksight supports day, hour, minutes but any custom groupings like this - but it could be done with a calc field.

Regards,
Ramon Lopez

1 Like
3

Dear Ramon,

Thank you for your answer.

Do you have any example?

Thanks

4

@Ramon_Lopez I tried this and it is not working

distinct_countIf({customer_id}, {stage} = ‘1’, [truncDate(‘HH’, {last_updated})]

5

@Ramon_Lopez

This calculated field is not working either

distinctCountOver(ifelse(in
({stage}, [‘1’]) AND ${TypeFilter} = ‘CL’
,{customer_id}
,null
)
,[truncDate(‘HH’, {last_updated})]
,PRE_AGG
)

6

Hi @July

What i meant was to create a calculated field for the date.

Newdatetime - I misunderstood the need here. Seems like the date/time needs to be recalculated based on the current time to look at the last 24hours. I would a. set time zone to the desired time zone and b. create a calculated field to subtract current hour from the hour in your data, then round to the day level. something like this.

addDateTime(24-extract(‘HH’,now()),‘HH’,{Order Date})

Would that work?

Thanks,
Ramon

2 Likes
7

I thought about bringing the date and time of the record and creating time range directly in the query, so in quick sight it would be easier, do you agree?

8

Hi July

Either way can be done - its up to your preference.

1 Like