Apply a time range to a KPI using filters

[aendr3w]

Hi.

I'm trying to set up date and time range filter so the user can extract data from any given time period on a specific date, but I'm struggling. It would preferably look something like this.

My first problem is with the time selection. I have a field with timestamps from productions bookings. Instead of selecting all the timestamps I wish to include I would rather have a start time and an end time in half hour intervals. If I round the existing timestamp field and call it Start_time and End_time in script I still get the same value in End time when I make a selection in Start time. There are other problems with rounding the timestamp field so i need a different sollution.

The second problem is when I try to apply it to my KPI. My expression looks something like this.

sum({1<datefield=P(datefield),Machine={'Drill'},Production_time={"09:00<=16:00"}>}PcsAmount)

Here is where I want o replace {"09:00<=16:00"} with my start and end time from the filter but i can't get it to work. I've been trying getfieldselections but I can't get i right.

I would be most thankful for any suggestions or pointers. 

Cheers / Andreas

[chrismarlow · Accepted solution]

Hi,

So just looking at the time element & on a toy set of data maybe use variable & drop downs. So drop downs on the right will change the times shown in the middle column;

To;

You would need to add more possible values to the drop downs and incorporate your date, but might be an option.

Added the QVF so you can see the set-up.

Cheers,

Chris.

[aendr3w]

Hi Chris.

I get this message when i import your app and then i get an Invalid visualization error on the start and end time dropdowns. Could you please paste the code you used in those?

[chrismarlow]

Hi,

Script is;

data:
Load * Inline [
Date, Production_time
30/06/2021, 08:50:00
30/06/2021, 09:00:00
30/06/2021, 09:20:00
30/06/2021, 09:30:00
30/06/2021, 09:50:00
30/06/2021, 10:15:00
];

LET StartTime=time('09:00:00');
LET EndTime=time('10:00:00');

Expression in the table is;

Only({<Production_time={">=$(=Time(StartTime))<=$(=Time(EndTime))"}>}Production_time)

Variable input set up like;

Think those would be the key bits, but let me know.

Cheers,

Chris.

[UncleRiotous]

There probably a slicker way of doing this but my solution is to create two separate fields in island tables which have the periods you want to choose from in the dataload and add filters for those.

ST_TIM:
Load * Inline [
START_TIME
00:00
00:30
01:00
01:30
etc, etc, etc
22:00
22:30
22:00
23:30
];

END_TIM:
NoConcatenate
Load
START_TIME AS END_TIME
Resident ST_TIM;

Then create three variables...

vDimStartTime                  =If(GetSelectedCount(START_TIME)>0,Time#(Min(START_TIME),'hh:mm'),Time#('00:00','hh:mm'))
vDimEndTime                    =If(GetSelectedCount(END_TIME)>0,Time#(Max(END_TIME),'hh:mm'),Time#('23:59','hh:mm'))
vExpTimeBetween           ='>='&'$(vDimStartTime)'&'<='&'$(vDimEndTime)'

vDimStartTime gets you the earliest start time selected.
vDimEndTime gets you the latest end time selected.
vExpTimeBetween gives you a > start time < end time you can drop into a set analysis.

Your measure then becomes

=sum({1<datefield=P(datefield),Machine={'Drill'},Production_time={"$(vExpTimeBetween)"}>}PcsAmount)

[aendr3w]

It works!

I was missing the Dashboard bundle so the variable input was giving me the error. Now it's working just as i wanted.

Thank you!