Friday 20 November 2009

Using Alerts in QlikView

Alerts are quite easy to set up. With a few tricks, they can become quite powerful.

Let me start with a very simple data set:

CountrySales:
LOAD * INLINE [
Country, Sales
England, 400
Ireland, 300
Scotland, 200
Wales, 100
];


Now, I want to create an Alert when there are any countries that have Sales > 200.

To create an alert, I need to be able to create an expression that will return a true value. I could use a simple aggr expression like:

=Sum(Aggr(If(Sum(Sales)>1, 1), Country)) > 0

This will work. However, it means that we are going to be limited to what information we can include in the Alert message. We can include the number of Countries, but we couldn't, for example, say which ones they were.

A more powerful way is to combine Advanced Search, Bookmarks and Alerts.

If I create an Advanced Search against the Country field such as:

=Sum(Sales)>200

Then I save this search as a Bookmark. Now, I can create an Alert with this Bookmark used where the Alert is simply:

=Count(Distinct Country) > 0

And the Alert message could be something like:

='Number of countries exceeding 200 = ' & Count(Distinct Country) & chr(13) &
'Countries: ' & Concat(Country, ', ')


Of course, I can bring any other information into this message that is related to the countries that match the Bookmark.

Now, if I set-up the "Mail" options under User Preferences, I can have this document email an alert to someone.

If I turn on the alert to use "Batch Mode", this can happen on the server when the document is reloaded.

11 comments:

  1. Pretty cool solution ! Thank you !

    A question arise, yet:
    How will this be easy to be configured by a client in Qv9, since Alerts seems to be gone from the QV menus?

    ReplyDelete
  2. In versions before v9, the Alerts were in the Settings menu. They have now been moved to the Tools menu.

    ReplyDelete
  3. how to create Alert from Macros in QlikView?

    ReplyDelete
  4. Hi,

    You can't create a new Alert using Macros (AFAIK) but you can interact with them once they have been created using GetAlert and GetAlertAndBookmark - see the API Guide.

    Stephen

    ReplyDelete
  5. Hello,

    Is it possible to run a macro when an alert is activated?. As far as I know, only is possible to send an e-mail or pop-up a window message, but the Qlikview help indicates it's possible also to run a macro.

    Thanks for your answer,

    G

    ReplyDelete
  6. Hi Gabriel,

    Only by interacting with the alerts using a Macro or external API.


    Stephen

    ReplyDelete
  7. Hi Stephen,

    Would you mind explaining how you save this expression as a bookmark:

    =Sum(Sales)>200

    Appreciate the help.

    Matt

    ReplyDelete
  8. When a task running in Publisher starts (or not) an alert where QlikView register the log information ?

    Many Thanks,

    ReplyDelete
  9. In what log file I can check if an alert was triggered or not?

    ReplyDelete
  10. Hello Everybody,

    I am trying to send an email alert on the refresh of the dashboard to the users who have not filled their time sheet .

    I found couple of posts in this community on how to make email alert/send mail.

    I want to know how I will proceed with that means what Condition I will put in the Alert Tab-> condition

    and do I have to write all the user's mail id(those who have not filled their Time sheet) under the mail recipients or I have to create any variable for that?

    Please help..


    Thanks in Advance
    Sonali

    ReplyDelete
  11. Hi Stephen I am trying to set up the mail option under user preference but I am having an error saying "Could not connect to server. Last Response. Unexpected SMTP Login response,Last Response:"

    I am using a yahoo account. So in the server section I did the following
    Address: smtp.mail.yahoo.com
    Port : 465
    Authentication Method : AUTH LOGIN
    UserID : simon@yahoo.com
    PassWork: *******

    After doing all of these and click on the Test button, I have the error message. Please can you help me solve these?

    Thanks

    ReplyDelete

Note: only a member of this blog may post a comment.