Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Dayna
Creator II
Creator II

Falling Trendlines/Sales

Dear all,

I am hoping that you could help me.... I am trying to achieve the below:

we have created a standard line chart showing the sum of sales over time, (with grouped dimensions to show Customer, product, etc.) and used the inbuilt function to show the trend lines. However, the user would like to be able to select something to show all negative trends (obviously this would change depending on the selectors) and potentially show a range of trends such as the dynamic buckets - all trends with -20% decline, 40%, 60% etc… I hope this is possible, but any help you could provide would be most appreciated!!

If you could provide an examples, or any assistance, I would be very grateful!!!

Kind regards,
Dayna

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

Well, the slope of the trend line by Date (for instance) should be this:

linest_m(aggr(sum(Sales),Date),Date)

If you want to group that by Customer and Product, I think you'd need those as dimensions, and this:

linest_m(aggr(sum(Sales),Customer,Product,Date),Date)

You should be able to put that in a list box by aggregating THAT across Customer and Product:

aggr(linest_m(aggr(sum(Sales),Customer,Product,Date),Date),Customer,Product)

And finally, you could group those into ranges. The class() function would make it easy, but you probably want more control, so you'd probably end up with a giant nested if() of some sort.

Eh, let me stop theorizing and put together an example. This sounds like a very interesting problem.

OK, I have an example up and working including being able to select positive and negative trends. However, since going from 120 to 100 is the same slope as going from 100 to 80, but they represent a different % decline, then this approach can't be directly extended to show % decline.

It might be possible to combine linest_m() and linest_b() functions into the actual point slope formula for the trend line, then apply that formula to both the minimum and maximum date in the range, and then calculate the % decline from those two values. I'd probably turn that formula into a variable so that I could refer to the value repeatedly while using nested if() statements to calculate my ranges. But I'll leave that alone for now.

Anyway, see attached for selecting by slope or by positive and negative slope.

View solution in original post

14 Replies
johnw
Champion III
Champion III

Well, the slope of the trend line by Date (for instance) should be this:

linest_m(aggr(sum(Sales),Date),Date)

If you want to group that by Customer and Product, I think you'd need those as dimensions, and this:

linest_m(aggr(sum(Sales),Customer,Product,Date),Date)

You should be able to put that in a list box by aggregating THAT across Customer and Product:

aggr(linest_m(aggr(sum(Sales),Customer,Product,Date),Date),Customer,Product)

And finally, you could group those into ranges. The class() function would make it easy, but you probably want more control, so you'd probably end up with a giant nested if() of some sort.

Eh, let me stop theorizing and put together an example. This sounds like a very interesting problem.

OK, I have an example up and working including being able to select positive and negative trends. However, since going from 120 to 100 is the same slope as going from 100 to 80, but they represent a different % decline, then this approach can't be directly extended to show % decline.

It might be possible to combine linest_m() and linest_b() functions into the actual point slope formula for the trend line, then apply that formula to both the minimum and maximum date in the range, and then calculate the % decline from those two values. I'd probably turn that formula into a variable so that I could refer to the value repeatedly while using nested if() statements to calculate my ranges. But I'll leave that alone for now.

Anyway, see attached for selecting by slope or by positive and negative slope.

Dayna
Creator II
Creator II
Author

Hello John,

That's brilliant! I'll have a look and see how my data will look... Many thanks for all your hard work!

Kind Regards,
Dayna

Dayna
Creator II
Creator II
Author

O, one last thing, I was thinking instead of hard coding it to 'Product' to use the active group within the chart.. I tried this:

=aggr(linest_m(aggr(sum([PDI Data - Sale Price]),getcurrentfield(Selectors),[PDI Data - Year Week]),[PDI Data - Year Week]),getcurrentfield(Selectors))

But I get no results, I can't see anything wrong with what I changed... Does getcurrentfield() work with aggr?

Kind Regards,
Dayna



johnw
Champion III
Champion III

Getcurrentfield() returns a text literal. You want the actual field. For that, you simply use "Selectors". QlikView recognizes that Selectors is a group, and automatically uses the current field from the group.

Dayna
Creator II
Creator II
Author

You're a star! Thank you!!

nathanfurby
Specialist
Specialist

John - that's a fascinating bit of code. Would you mind taking the time to explain how it even works?! I am a newbie so go easy.

I cannot see how the List Box is managing to filter the results in the way that it does. Where is the link created between the list box and the only table in the view?

johnw
Champion III
Champion III

Let's start with the list box of the slopes. It uses this expression:

aggr(linest_m(aggr(sum(Sales),Customer,Month),Month),Customer)

The key here is the linest_m() function:

linest_m(aggr(sum(Sales),Customer,Month),Month)

The basic form of this is:

linest_m(Y,X)

Where Y and X are pairs of points that we're calculating the slope for the trend line of. Or as the reference manual says, "returns the aggregated m value (slope) of a linear regression defined by the equation y=mx+b for a series of coordinates represented by paired numbers in x-expression and y-expression iterated over the chart dimension(s)." So give it a table of Y and X values, and it will give you the slope.

Unfortunately for us, we don't HAVE a table of Y and X values to give it. We have to build one in the expression, which is what the aggr() function does - build an internal table:

aggr(sum(Sales),Customer,Month)

So THIS is basically giving us our data points. For each Customer, for each Month, calculate a Y value that is the total sales for that customer and month. I hate jumping around, but I need to mention that since the original expression encloses all of this in aggr(...,Customer), we're really only looking at a SINGLE customer at this point. So in a sense, we're just looking at, say, Customer A's sales by month, and the aggr() here builds us that little internal table of Y and X values, just like we need. Working our way outwards from there:

linest_m(aggr(sum(Sales),Customer,Month),Month)

We've added linest_m(...,Month). So we're asking QlikView to calculate the slope of the trend line for the sales of the customer by month. Finally, we add back in the outside aggr so that we do this for each customer:

aggr(linest_m(aggr(sum(Sales),Customer,Month),Month),Customer)

The outside aggr thus returns an internal table of the slopes for each customer. Since we put this as the expression in a list box, it converts this internal table into a list of the slope values. And they aren't just numbers - QlikView KNOWS that these are the slopes for customers, so when we select a slope, we ACTUALLY select a customer. If you put a customer list box on the page, you can see it happen - the customer with that slope goes green, and the others go gray.

If that's understood, then we can move on to our list box that just indicates if the slope is positive or negative. It is VERY similar. The only difference is that instead of returning the slope itself, we put an if() around the slope, and just return if it is positive or negative.

aggr(if(linest_m(aggr(sum(Sales),Customer,Month),Month)>0,'Positive','Negative'),Customer)

Once again, QlikView is keeping track internally that we have a table of Customers and whether or not the associated slope for that customer is positive or negative. So again, if we select a value in that list box, QlikView ACTUALLY selects a customer or customers.

The slope list boxes aren't connected to our chart in any direct way. They simply use expressions that reproduce exactly what is in our chart, and then pull out bits of data from the chart, in this case the slope of the trend lines. Since selecting something in our list box selects an actual customer or customers, it then affects the chart exactly like we would expect.

nathanfurby
Specialist
Specialist

Many thanks John. This is very interesting and has certainly expanded my understanding of what is possible with QV.

Not applicable

Hi John.

Thank you very much for your topic, and for your patience to explain all steps.

One question: I have the linear equation y = Ax + B , right?

This expression aggr(linest_m(aggr(sum(Sales),Customer,Month),Month),Customer) returns the A.

Can you help me to find the B?

My problem is that I don't know exactly one point to put in this equation to find the B.

Best Regards
Julian