Hi everybody, I'm Mike Fawcett
Senior solution architect with Qlik Pre-sales
organization
In this Qlik Help video I'd like to
show you how to set up a trend analysis in Qlik Sense
Which is then used by Qlik Alerting to
trigger alerts in case of downward
trends
As you can see in this table, we have
product level details of sales in a retail company
By order ID and by order date
You'll also notice that we have a line
chart with the daily sales
and we've included a linear trend line
in this particular line chart
In this use case scenario I want to be
alerted if I have sales to customers
which are trending positively
over time
they are then trending downwards
Let's take a look at an example here of that
particular customer
And as you can see the overall linear
trend
is positive however in the last 365 days
the trend is actually heading downwards
and that's the
exact kind of use case scenario that we
want to use with Qlik Alerting
scenario happening where we have a
positive trend over the entire let's say
length of time
starting to trend downwards
and specifically
the KPIs that we have here on the right
and how we are calculating those so i'm
going to edit this sheet and go into my
master items
First one we're going to take a look at
is the sales trend
master item and this particular master
item
is exactly going to look at how do we
calculate whether
the trend from the start of the data to
the end of the data
is positive or negative and in this
particular case we are going to use the
Linus_m function in Qlik Sense
and essentially the Linus_m function
gives us a single value which is either
going to be
a positive number or a negative number
that the slope of the trend is positive
A negative number will indicate that the slope
is negative
help
to understand more about how to use a
a function such as the linus_m
is the Y value which is the values of in our case the sales
And the X value which is also then the dates of the sales
Since we have
multiple lines of data per order date
what we need to also do here is we need
to aggregate the sum of sales by order date
And we're we're using also in
this particular expression and this is
important for then pushing this data over to Qlik Alerting
we're using what's referred to
as a structured parameter for customers
who are basically forcing the aggregate to happen on the customer dimension
and then where we see order date here
this is the
X value so effectively we have this
whole statement here
date of the sales
and then the order date over here is the
X value
You'll also notice that since we have
the opportunity here to understanding that
if this is a positive result that we can
color this green if it's going to be a negative
result then we can color this
orange
here as well with this particular master item
based on that condition
What is the sales trend in the last
365 days so let's take a look at that master item
master item
And you'll notice here that we're using
the exact same function of lines_m
And this part of the expression has not
changed from the previous
master item that we were looking at
however what's different now
is we have added a set analysis based on
order date
and so we basically only want to look at
orders that have an order date that
are greater than
the basically the result of what is today minus 365 days
So what I like about that set analysis
is that this basically makes this a
rolling analysis so
It can continue to look back to the last 365 days
And finally we have one last
Important master item here which is the
days with sales in the last 365 days
let's take a quick look at that
if you're going to do an analysis of a
trend in data
you want to have at least three data
points
otherwise you're not really looking at
potentially a trend
and so we wanted to set up a
master item here which is simply a count
of the distinct number of order dates
again using this set analysis of only
looking at the orders in the last 365
days
and so quite and again the reason we're
doing this is
if we're going to be looking for trends
in the last 365 days then let's
evaluate customers with orders that have
a count of at least three orders in the
last 365 days
With all of that said let's now move to
the administration program of Qlik  Alerting and now set up
the alerts for customers who have a
positive trend over time but in the last
365 days is trending downwards
Okay everybody so here we are in the
Qlik Alerting
administration console and we're going
to go ahead and create our new alert
to look for downward sales trends
in the application we had just created
which is our
rolling sales trend analysis
and at this point we're going to add
those three master items that we had
created
in our Qlik Sense application
to this particular alert
so as you can see I've added the days
days with
sales in the last 365 days
The overall sales trend and then the sales trend in the last 365 days
Now, what's important here is
that we want this analysis to be based
on
customers of course we could have
configured this also
to be based on products or product
categories
where we're looking for trends over time
and compared to trends in the last 365
days
just make sure you adjust your master
item accordingly
we have already a preview of the first
10 rows of data so already you can see
our customers the number of days with
sales in the last 365 days
the overall sales trend compared to the
sales trend in the last 365 days
We will now go to the
next screen where we will set up our condition
The first condition we're going to use
is the days with sales in the last 365 days is going to be a manual value
and it should be greater than 
or equal to 3
We're going to add a second condition
Which is going to be the sales trend
and again this is looking at the sales trend
over the length of the data that we have
and we want this to be greater than zero
so basically basically that condition
will pick up
the customers that have an overall
positive sales trend
and now we are going to pick the sales
trend in the last 365 days this again is
going to be
a manual value
and we want this to be
less than zero
so we've added the the three conditions
now we since we have these multiple
conditions what I'd like to do
is add a condition rule
which is that I want to look for conditions where A and B
and C are true
Let's go ahead and click
on the preview and validate those conditions
And as you can see in the validation
We have out of a total of 90 customers 11
 who actually meet
that condition or these three conditions together
Whereas we have 79 that do not meet
those conditions and already you can see
the customers here that have in this
case
14 days with sales in the last 365 days
overall they have a positive sales trend
however the trend in the last 365 days
is trending downwards
So I'm going to go in next I'm going to
accept the default in this case that the
we're going to evaluate for these
conditions or this condition in the data
only on the reload of the data
application
and I'm going to accept the defaults for
the distribution which is going to be by email
and we're going to leave the
notification
as the default notification that comes
with Qlik Alerting as well
So, in this case now the alert has been
created
and we're going to go ahead and trigger that
alert
the alternative is I could also go into
my QMC in Qlik Sense and do a reload of the data but
You can simply come here to Qlik Alerting as well and click on trigger
So, at this point I'm going to receive
two emails one email will be the
confirmation that we've created the alert
and the second email should be the alert itself
notifying me that these particular customers
meet these particular set of conditions
let's take a look at those emails now
Okay, so here we are with our email
notification that our alert has been
created we see the three measures that
have been used from
the Qlik Sense application
the dimension that we're using as our drill down dimension
and then the condition and the particular rules
since I had triggered the rule to
execute you can now see the email
notification that I received from Qlik Alerting
Regarding the 11 customers that meet
this particular condition and set of
rules down below
since we're using the drill down
dimension of customer
you can see the results that the
customers themselves the days with sales
in the last 365 days, the sales trend
overall
and the sales trend in the last 365 days
and to go back to the example that we had
started with
I'm going to click on this particular customer here
And this is going to open up our Qlik Sense application
and bring us directly to
the same sheet
With the filter already set
on the customer that we had selected so
we can do further analysis
so I hope you've enjoyed this Qlik Help
video on how to set up
a trend analysis in Qlik Sense and then
how to bring that into Qlik Alerting to
trigger alerts, based on the scenario of trends that
are positive over time but in the last 365
days are trending downwards
See you in the next Help video
