topic Re: Help dimension in set analysis in QlikView

Help dimension in set analysis

Mon, 26 Jan 2026 18:19:17 GMT

hi, I have two separate tables.
fields:
table 1) login (datetime), logout (datetime), session (integer)
table 2) date (datetime)
I would like to create a tabular graph where the dimensions have the date field. I would count the sessions where the login field is less than the date field and loguot greater than the date field.
Now I use this expression,

=aggr ( Count ( { < login = {'<=$(=date)'} >*<logout = {'>=$(=date)'} > } session ) , date)

and it only works with a selected date, while I need the full list of dates and the relative count.
Can someone help me?
thank you so much

Re: Help dimension in set analysis

varshavig12 — Mon, 20 Jun 2016 09:28:58 GMT

Can you provide us with some sample data ?

Re: Help dimension in set analysis

Mon, 20 Jun 2016 09:38:10 GMT

TABLE 1:

login	logout	session
20/06/2016 06:28	20/06/2016 06:45	529035
20/06/2016 06:42	20/06/2016 06:47	529036
20/06/2016 06:48	20/06/2016 06:48	529037
20/06/2016 06:48	20/06/2016 06:49	529038
20/06/2016 06:49	20/06/2016 07:37	529039
20/06/2016 06:51	20/06/2016 07:15	529040

TABLE 2:

date

10/06/2016 13:30:00

10/06/2016 14:00:00

10/06/2016 14:30:00

10/06/2016 15:00:00

10/06/2016 15:30:00

10/06/2016 16:00:00

Re: Help dimension in set analysis

sasikanth — Mon, 20 Jun 2016 09:56:06 GMT

Try this,

use intervalmatch,

Table1;

Load Login, Logout,Sessio from table1;

Table2:

Load Date from Table2

left join(Table1)

intervalmatch(Date) Load

Expression:

Count(if(not isnull(Login) and not isnull(logout),session))

Re: Help dimension in set analysis

Mon, 20 Jun 2016 11:03:00 GMT

Eureka! It's perfect!

... but ...

it's possible to create a table like my "table 2" (that i've created manually) automatically?

Can I give a specified date and time interval (for example every 5 minutes)?

thank you so much!!!!!

Re: Help dimension in set analysis

sasikanth — Mon, 20 Jun 2016 14:18:07 GMT

Try this script,

need to modify max_date if you want to get it in a dynamic way

Data:
  Load * INLINE [
  New_Date
  5/4/2014
  5/6/2016
  ];

Let vVal=(5/1440); // 5 min interval

Temp:
Load    Max(New_Date) as Maxdate,
            Min(New_Date) as MinDate
Resident Data;

LET vMinDate=num(Peek('MinDate',0,'Temp'));
LET vMaxDate=num(Peek('Maxdate',0,'Temp'));

Temp_Calendar:
LOAD       
          If($(vMinDate)=$(vMinDate) + Iterno()-1, Timestamp($(vMinDate)+$(vVal)) ,Timestamp(peek('Required_Date')+ $(vVal)))  as Required_Date
            AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

Re: Help dimension in set analysis

Mon, 20 Jun 2016 15:13:08 GMT

I tried the script, but unfortunately I have problems. The first date is correct (04/05/2014 00:00:05) but the while statement will stop after 3 days at 18:05:00.

Re: Help dimension in set analysis

Tue, 21 Jun 2016 11:31:50 GMT

I think to do something useful if I attach this script that generates an automatic datetime to a specific time range. They welcome corrections or additions.

tempDate:

LOAD

'10/06/2016' as MinTimestamp,

'20/06/2016' as MaxTimestamp

\\Resident table;

LET vMinDateCal = num(peek('MinTimestamp',0,'tempDate'))+1;

LET vMaxDateCal = num(peek('MaxTimestamp',-1,'tempDate'));

Temp:

LOAD

TimeStamp($(vMinDateCal) + (RecNo()/6/24) + (IterNo() -1)) as DateTimeStamp //every 10 minutes

AUTOGENERATE ($(vMaxDateCal)-$(vMinDateCal)+1)*144;

DROP Table tempDate;