Not active Clients?

[#1]

Hi fellow qlikviewers.

I have a yearly .QVD which I use for statistics, that I have scheduled to reload every night with new data from the database.

With this I would like to see maybe which client that has NOT been active for the last 7 or 30 days.

Is it doable in Qlikview?

[MK_QSL]

Yes... Provide sample data to work..

[Ricardo_Gerhard]

Hi Niklas,

   There is a Qlikview Governance Dashboard that read all log files and you can see your users access, aplications complexity, and a lot of informations.

   So, if you want to analyze your clients access on your system, you need to load this data on your application with a field like "Last_Used"/"Last_Access" and create a "<" expression.

   Does it help you?

Regards.

[#4]

Analysis:
LOAD TradeDate,
AccountId,
MarketId,
ExecutionVenueId,
ValueName,
CurrencyId,
Value,
ValueSEK
FROM

(qvd);

Accounts:
LOAD AccountId,
ClientId,
AccountShortName,
AccountLongName,
ParentAccountId,
Type,
CountryISOCode,
City,
Manager,
ManagerId,
Trader,
TraderId,
RegisteredDate,
DeregisteredDate,
Sales,
SalesId,
BackupTrader,
BackupTraderId
FROM

(qvd);

Notes:
LOAD AccountDate,
AccountId,
Cancellation,
Commission,
CSA,
CurrencyId,
IsBuy,
ISIN,
MarketId,
NoteId,
Price,
SettleDate,
ShortName,
TradeDate,
Volume
FROM

(qvd);

Trades:
LOAD AccountId,
AccountRef,
ClientId,
CounterpartId,
CurrencyId,
ExecutionVenueId,
IsBuy,
ISIN,
LiquidityIndicator,
MarketOrderId,
OrderbookId,
OrderId,
Price,
ShortName,
TradeDate,
TradeId,
Volume
FROM

(qvd);

[Peter_Cammaert]

If it's not document access that you want to monitor, but simply the most recent activity by customers as stored in your database, then what field can be used to figure out the last activity date?

Peter

[#6]

Accountid, TradeDate I would say. What accountId has not been active the last 7 dates based on tradedate

[Ricardo_Gerhard]

Niklas, you can filter users simplely typing

TradeDate<"d-7".

Try it.

[Peter_Cammaert]

Since you reload the data every day, add this to the bottom of your example script:

:

LEFT JOIN (Accounts)

LOAD AccountId,

     IF (Max(TradeDate) < Today()-7, 1, 0) AS InactiveInLast7Days

RESIDENT Trades

GROUP BY AccountId;

If you don't want this flag to become part of your Accounts table, LEFT JOIN the load into Trades itself.

Best,

Peter

[Edit] The JOIN/GROUP BY field is called AccountId, not AccountID

[#9]

I see you understand what im looking for. But the syntax is incorrect getting "Invalid expression" message when I run the script.

[Peter_Cammaert]

And you want me to guess what syntax you are using? That may cost us both a lot of time. Please post that part of your script that contains the base table Load and the JOIN you get errors on.

It works for me:

Best,

Peter

[Edit] Sorry, you're right. I used the wrong table name. Replace Accounts with Notes in the LEFT JOIN clause.