Set analysis on a fixed range of time

[Fenril]

Greetings smart folks,

First of all, I am a true Qlik newbie trying to figure out how to improve some app developed a while ago. I learned a few things browsing this community forum and reading the doc but I have been stuck for some time now and I would appreciate your help to get me to the next step.

Here are my issues.

I have two database providing me with data related to operators working on multiple places along the day.
The first one is a qvd providing me with forecast of expected users (EX_#EXP_U). The second is a reporting database allowing me to know which users are currently working (OP_USER).

These two tables provides information on a cycle of 10 min (value as varchar like 06:00) and for a list of multiples zones, lets say A B C D

I am working on providing adherence information between expected and actual users and I simply created a chart and a couple of variables as follow.

My basics variables are :

vExpU : 

sum (Ex#EXP_U)

vActU : 

count (distinct OP_USER)

vDifU :

fAbs($(vActU)-$(vExpU))

The adherence indice variable is as follow :

vAdhi :

if($(vDifU)>1, $(vDifU)-1, 0)

This allows me to get a detailed chart showing what a want as long a I manually filter by zone (i didn ‘t included the zone in the dimension)

 At this point I am quite satisfied as if I wish to know the total indice for a certain amount of time and for a specific zone, the sum of the lines will be ok.
The problems starts when I try to get that sum for a fixed time period like a morning shift or when I would like to sum indices of multiples zones.
Here is what I tried for the morning shift.
Since what I want is the sum of the values of vAdhi, per 10 min, between let say 06:00 and 14:00 I created a new variable using set analysis :

vAdhIMs : 

sum({<OP_HH2 ={">='06:00' < '14:00'"}>}$(vAdhi))

and it doesn’t work.

I tried also to get a chart using the zones in dimension and the following

Aggr(sum ({<OP_HH2 ={">='06:00' < '14:00'"}>}$(vAdhi)),OP_HH2, OP_ZONE))

but I am obviously missing something.

Like I said I tried many things that confused me more than it inlighted me at some point therefore I would realy appreciate a little help to put me back on tracks.

Thank you.

[marcus_sommer]

I wouldn't do in this way else using an appropriate categorizing within a master time-table and then:

sum({<Hour ={6,7,8,9,10,11,12,13,14}>}$(vAdhi))

or

sum({<Shift ={1}>}$(vAdhi))

[Fenril]

Good morning Marcus, 

Thank you for your reply.
I have been reading about the master Calendar/Time table implementation and I assume the main advantage would be to work with unique time values instead of the multiples ones present in both the tables and in the synthetic key ?

[marcus_sommer]

The benefits of a master calendar/timetable are:

• to get all values as unique values without a gap between the values
• it's very easy to create and kept the fact-table small because only one key-field is needed
• could contain dozens to hundreds of information, for example as pure number, as string, as dual-value, with various formatting (each kind of calculating and matching should be done with pure numbers)
• all kinds of period-requirements like flagging & accumulating of working-days, flagging YTD, MTD and many more could be added
• needs only to be created ones for the entire environment for all times and each application grabbed this qvd and picked the needed periods and fields

The advantage of such main-approach increased further if there are needs for multiple calendars - independent ones or connected in any way like the canonical calendar or ones with an as-of-table logic.

Personally I would resolve the synthetic key between times and time-zone because IMO they are bad. There are of course difference opinions to this topic and just such a single two field synthetic key will work for the most things (exceptions are for example aggr() and total statements) but if there are several synthetic keys and even synthetic keys between synthetic keys ... 

I think I would probably in your case using multiple time-fields within the timetable. Possible would be also to create appropriate offset-values in this timetable or maybe also linked within another table. And also the usage of an as-of-table would be a possibility.