Generate All minutes for each Day

[rustyfishbones]

Hi All,

I have the following in my script to generate OneMinuteBuckets from a StartTime

TIME(FLOOR(TIME#(StartTime,'hh:mm:ss'),1/24/60),'hh:mm')AS OneMinBucket,

The problem I have is, I always show the data in the chart for 1 day, but I want to see all minutes from 6am to 11pm.

But if there were no transactions in a particular minute (which can happen regularly), I still want to generate a dimension for that minute

So if a worker does the first transaction at 09:01 and the second at 09:05

I still want to display 09:01,09:02,09:03,09:04,09:05

So I want my chart would look similar to below

instead of this

[sunny_talwar]

May be use a master time table

The Master Time Table

[Aurelien_Martinez]

Hi,

This script generate all date time during one year and 6am to 11pm :

data:

LOAD

  Date(Today() + RecNo()) as Date

AutoGenerate(365)

;

Join(data)

LOAD

  Time(Time#(6, 'hh') + Time#(RecNo()-1, 'mm'), 'hh:mm') as time

AutoGenerate 16*60 + 1

;

data_f:

LOAD

  Date#(Date & ' ' & time, 'DD/MM/YYYY hh:mm') as dateTime

Resident data

;

[effinty2112 · Accepted solution]

Hi Alan,

Maybe:

Let vStart = Time#('06:00','hh:mm');

Let vEnd = Time#('23:00','hh:mm');

[Master Clock]:

LOAD

Time(Temp_Time/1440) as [Clock Time];

LOAD

num('$(vStart)')*24*60 -1+  IterNo() as Temp_Time

AutoGenerate 1 While num('$(vStart)')*24*60 + IterNo() -1<=  num('$(vEnd)')*24*60;

Cheers

Andrew

PS

you might prefer this

Let vStart = Time#('06:00','hh:mm');

Let vEnd = Time#('23:00','hh:mm');

Let vStartMinute = num('$(vStart)')*24*60;

Let vEndMinute = num('$(vEnd)')*24*60;

[Master Clock]:

LOAD

Time(Temp_Time/1440) as [Clock Time];

LOAD

$(vStartMinute) -1+  IterNo() as Temp_Time

AutoGenerate 1 While $(vStartMinute) + IterNo() -1<=  $(vEndMinute)

[#5]

Alan, you can simply generate with below statement:

Let vToday = Num(today());

Let vNoofDays = 30;

Load

     $(vToday) - ((IterNo()-1)/(24*60)) as DateTimeField

Autogenerate 1

While IterNo() < $(vNoofDays) * 1440;

[rustyfishbones]

Thank you everyone,

Let me try them and see which suits best

[Aurelien_Martinez]

Smaller,

data:

LOAD

  Date

Where Num#(Timestamp(Date, 'hh')) >=6 and Num#(Timestamp(Date, 'hh')) <= 22

;

LOAD

  Date(Today() + Date#(RecNo(), 'mm'), 'DD/MM/YYYY hh:mm') as Date

AutoGenerate(365*24*60)

;

[rustyfishbones]

Hi All,

I think I have the minutes sorted, so see the attached where my chart will not show the white space between non pick transactions

Green Means a pick happened for more than  2 cases in that minute

Red Means a pick happened for less than  2 cases in that minute

White means no pick happened in that minute

I am hoping for something similar to this, but by minute rather than Days

Regards

Alan

[effinty2112]

Hi Alan,

You could try the old trick of adding Sum({1}0) to your expression so it will return a 0 rather than null. It's not perfect - it can become a headache if you're making anything other than simple selections.

cheers

Andrew

[rustyfishbones]

Tried that and it didn't work - I have been trying to get this chart working for months now!!