topic Falling Trendlines/Sales in QlikView

Falling Trendlines/Sales

Dayna — Thu, 01 Jul 2010 10:00:08 GMT

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

Falling Trendlines/Sales

johnw — Thu, 01 Jul 2010 23:07:08 GMT

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.

Falling Trendlines/Sales

Dayna — Wed, 07 Jul 2010 14:06:22 GMT

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

Falling Trendlines/Sales

Dayna — Wed, 07 Jul 2010 14:53:39 GMT

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

Falling Trendlines/Sales

johnw — Wed, 07 Jul 2010 17:06:06 GMT

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.

Falling Trendlines/Sales

Dayna — Thu, 08 Jul 2010 09:22:28 GMT

You're a star! Thank you!!

Falling Trendlines/Sales

nathanfurby — Thu, 08 Jul 2010 15:38:57 GMT

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?

Falling Trendlines/Sales

johnw — Thu, 08 Jul 2010 17:47:55 GMT

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.

Falling Trendlines/Sales

nathanfurby — Fri, 09 Jul 2010 14:14:30 GMT

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

Falling Trendlines/Sales

Tue, 30 Nov 2010 01:47:53 GMT

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

Falling Trendlines/Sales

johnw — Tue, 30 Nov 2010 01:57:16 GMT

Just replace linest_m with linest_b.

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

However, if you're creating a linear equation, this will only work if you have a dimension of customer, as it will generate a separate slope and Y-intercept for every Customer. I assume that's what you're after, but if not, you'd remove the outside aggr to get a single linear equation across all customers.

Falling Trendlines/Sales

Tue, 30 Nov 2010 02:04:24 GMT

Hi John.

I tried this, but the number don't match.

In my app, I don't have any other dimension, just the Period.

Thank you very much again... for your help...

Regards
Julian

Falling Trendlines/Sales

johnw — Tue, 30 Nov 2010 02:12:27 GMT

Sounds like this is the same as your question in the other thread, then, in which case I'll refer you to my answer and example in that thread. If you want to verify that the example in that thread is producing the correct linear equation, you can turn on the linear trendline for the first expression, and the two lines should overlap.

http://community.qlik.com/forums/t/24358.aspx

linest_m(total aggr(sum(Sales),Month),Month)*Month
+linest_b(total aggr(sum(Sales),Month),Month)

Falling Trendlines/Sales

Tue, 30 Nov 2010 02:25:57 GMT

Hi John

I'll try to do this in my app whit your app as example.

One more time, thank you for your reply and help.

Regards
Julian

Re: Falling Trendlines/Sales

Thu, 06 Nov 2014 10:07:10 GMT

Dear John,

do you see any easy way to get the X and Y values of a specific trendline point?

My current idea is to have a reference line which intersects the trendline and have the coordinates displayed somewhere.

The reference line would be placed by a user input field or a slider.

Best regards

Fridtjof