Create category

[Manni_SM]

Hi All,

@rubenmarin  igoralcantara  Vegar

i want to create below category group in script  for session monitor application can someone help on this ?

am new to qlik

[rubenmarin]

Hi,I don't hace easy access to a Sessions Monitor I can make tests. I n front end it could be done using a calcualted dimension like:

=Aggr(If(Sum({<Date={">=$(=Date(Today()-30))"}>} [Session Count])<2,'Infrequent'
,If(Sum({<Date={">=$(=Date(Today()-30))"}>} [Session Count])<5,'Regular'))...
,UserId)

The session count field is in the LogContent table, wich alsxo has the UserId field and LogEntryPeriodStart can be used as reference data, so maybe you can just add the new field to the LogContent table adding a script like:

Left Join (LogContent)
LOAD
  UserId,
  If(Sum(SessionCount)<=2
    ,'Infrequent'
    ,If(Sum(SessionCount)<=5
      ,...
Resident LogContent
Where LogEntryPeriodStart>=Floor(Today()-30)
Group By UserId;

[Manni_SM]

sure @rubenmarin  thanks i will try and get back to you 

[Manni_SM]

Hi @rubenmarin  

below is my script and i got the new filed with bucket range but its not showing last 30days

and i want to calculate below 6/90/180 login count how to do this in below script ?

session_count:
Load count(Id) as count resident LogContent
Where [Session Count]=1;

LET session_count = Peek('count');
Drop table session_count;

IF session_count > 0 Then

TRACE Working on Session Summary for $(session_count) session entries.;

SessionSummary:
NoConcatenate Load
_proxySessionPackage, // LINK
LogEntryPeriodStart as [Session Start],
LogTimeStamp as [Session Finish],
Hostname as [Session Hostname],
[App Name] as [Session App Name],
[Session Duration],
[Session Count]
RESIDENT LogContent
WHERE [Session Count] = 1 ;

// Add Session_Engine fields for calculation of "Cost of a Session"
Session_Cost:
NoConcatenate Load
_proxySessionPackage, // Key to link the session
[Session Selections],
[Session CPU Spent (ms)],
[Session KBytes Sent+Received]
Resident LogContent
Where [Session CPU Spent (ms)] >=0;

/* added for bucket logic */

ActiveuserGroupBucket:
LOAD
UserId,
If(Sum([Session Count])<=2
,'Infrequent',
If(Sum([Session Count])<=5
,'Regular',
If(Sum([Session Count])<=10
,'Enthusiast',
If(Sum([Session Count])<=20,
'Motivated','HiglyMotivated')))) as ActiveuserGroupBucket

Resident LogContent
Where LogEntryPeriodStart>=Floor(Today()-30)
group by UserId; 

[rubenmarin]

Hi, the condiiton "LogEntryPeriodStart>=Floor(Today()-30)" is the one used to get the last 30 days.

I don't have access to an app where I can make tests, maybe LogEntryPeriodStart needs a Date#() to convert to date.

An to create new buckets you can dosomthing similar to create the busckets:

- First the where conditions should get all the needed logins, like "LogEntryPeriodStart>=Floor(Today()-180)"

- Then you can create the bucket for each login with an If, like

If(LogEntryPeriodStart>=Today()-6, 'Last 6 days'

  ,If(LogEntryPeriodStart>=Today()-0, 'Last 90 days', 'Last 180 days')) as DaysBucket

[Manni_SM]

Thank you . @rubenmarin 

below is the  format for LogEntryPeriodStart

[rubenmarin]

If it's right-aligned it means that it's a number, so the numeric comparison should work.

[Manni_SM]

Okay thanks for suggestions let me try and update 

[Manni_SM]

Hi @rubenmarin 

is this looks fine or do i need to change any code ? for both  session count and  login range 6/90/180 days?

ActiveuserGroupBucket:
LOAD
UserId,
If(Sum([Session Count])<=2
,'Infrequent',
If(Sum([Session Count])<=5
,'Regular',
If(Sum([Session Count])<=10
,'Enthusiast',
If(Sum([Session Count])<=20,
'Motivated','HiglyMotivated')))) as ActiveuserGroupBucket

Resident LogContent
Where Date#(LogEntryPeriodStart)>=Floor(Today()-30)
group by UserId;

loginCountRange:
LOAD
UserId,
If(LogEntryPeriodStart>=Today()-6, 'Last 6 days'

,If(LogEntryPeriodStart>=Today()-0, 'Last 90 days', 'Last 180 days')) as DaysBucket

Resident LogContent
Where Date#(LogEntryPeriodStart)>=Floor(Today()-180)
group by UserId;

[rubenmarin]

The loginCountRange won't work, if you want to set the bucket at user level you could use Max(LogEntryPeriodStart) to use the most recent login of each user.

If you want the same user to have logins in last 6 days, last 90... depending of each login date, then you can't group by UserId.

If you want to use the date of each login, you can add the If for the bucket in the same LogContent table.