15 min interval time

[AuroraK]

Hi community, 

Seeking your assistance in creating an chart dimension, which shows 15 min interval time for the max date. 

Thanks in advance

[SterreKapteijns]

Hi!

Do you mean you want to have all timetamps of the max date on the x-axis with increments of 15 minutes? 

If so, I think it would be good to create everything in the load script:

• MaxDate = Max(DateField); //Get the maximum date of the datefield
• TempTimeTable: LOAD Timestamp(Timestamp#(Time(0, 'hh:mm:ss')) + (IterNo() - 1) * 1/96) as TimeValue AUTOGENERATE 96; // 24 hours * 4 quarters an hour 

  // create all timestamps with steps of 15 minutes
• TimeTable: LOAD Timestamp(MakeDate(MaxDate) + TimeValue) as DateTimeValue RESIDENT TempTimeTable;

  // create the date with the timestamp
  
  Now you can just use the field DateTimeValue in the chart. 

[Qrishna]

Your description is really vague with no demo data or desired output.

From waht i understood, i believe you need to show something like below:

if so, try below:

// Load the raw data
MaxDateData:
LOAD
Date,
1440 as MinuteinaDay
INLINE [
Date
09/23/24
10/01/24
10/08/24
09/17/24
];

// Find the maximum date
MaxDate:
LOAD distinct *,
//IterNo() as MinuteIndex, // Create a minute index for each 15-minute interval
Floor((IterNo()-1)/15) * 15 & ' - ' & (Floor((IterNo()-1)/15) * 15 + 15) as MinuteRange // Create the 15-minute interval ranges
WHILE IterNo() <= 1440; // Loop until the minute in a day is exhausted to create intervals
LOAD
Max(Date(Date, 'MM/DD/YY')) as MaxDate
Resident MaxDateData;

[SunilChauhan]

explore something like this function  ValueLoop(from [, to [, step ]]).

[AuroraK]

Thanks for your reply, here is the dimension I created for my chart "maketime(hour(start_time), (floor(Minute(start_time)/15))*15". Where start_time is date and time field. Currently it's showing the time breakdown from 12:00:00 AM to 11:45:00 PM, for the entire dates. I need to only present it for the max(date). If that makes sense.

[ArcticSparks]

You're welcome! Thanks for sharing the dimension you created. To only show the time breakdown for the max date, you could try adding a filter or condition that limits the data to just the maximum date. For example, you can modify your calculation to something like this:

IF date(start_time) = max(date(start_time)), maketime(hour(start_time), (floor(minute(start_time)/15))*15), NULL

This will ensure that your time breakdown only shows for the latest date in your dataset. Let me know if that helps or if you need any further adjustments!

[diegozecchini]

Hi Aurora!
To create a 15-minute interval time dimension for the maximum date in Qlik, I would use a calculated dimension.

First, identify the maximum date in your dataset using the Max() function. You can store this in a variable or directly use it in your expression.
Then use the Floor() function to round the timestamps to the nearest 15-minute interval. Here’s a step-by-step expression:

=Timestamp(Floor([YourTimestampField], 15/1440))
YourTimestampField: Replace this with the actual field that contains your timestamp data.
15/1440: This fraction represents 15 minutes in terms of days (since 1 day = 1440 minutes).

Next, to filter by the maximum date, you can use an additional condition. You can create a calculated dimension or set analysis in your measure, limiting it to the maximum date:

=If(Date([YourTimestampField]) = Max(Date([YourTimestampField])), Timestamp(Floor([YourTimestampField], 15/1440)))

Once you have this expression, you can add it as a dimension in your chart.

Example:
If you have a TransactionTimestamp field, your calculated dimension might look like this:

=If(Date([TransactionTimestamp]) = Max(Date([TransactionTimestamp])), Timestamp(Floor([TransactionTimestamp], 15/1440)))

This will show the 15-minute intervals for the maximum date in your dataset.

let me know if helps

[AuroraK]

Thanks everyone for your replies, I tried your mentioned comments but couldn't find any luck in getting it right. Here's what I am thinking of creating my dimension as:
=If(
GetSelectedCount(Date) > 0,
maketime(hour(start_time), (floor(Minute(start_time) / 15)) * 15),
maketime(hour({< Date= {"=$(=Date(Max(Date)))"} >}start_time), (floor(Minute({< Date= {"=$(=Date(Max(Date)))"} >}start_time) / 15)) * 15)
)

The else statement doesn't seems to work, so if anyone can help me get this right would be much appreciated. Thanks!