topic Re: Creating a trend function on Qlik in QlikView

Creating a trend function on Qlik

marikabi — Wed, 25 Nov 2020 16:16:04 GMT

Hello Everyone,

I'm new on Qlik View.

I have uploaded a file excel with these columns:

REGION, STORE, QUANTITY SOLD.

The excel file is made of 13 different sheets: CURRENT MONTH, MONTH-1 -- MONTH-12.

What I need to do is to determine if the sales are growing or not and how fast for every REGION.

I'm trying to do it using a pivot and the LINEST_M function, but I'm not able to group the sales for region:

I mean, it works for the stores, but in every region there are more stores.

I think I have to modify the script, but to be honest I'm not able to do it.

Could you please kindly help me?

Thanks in advance

Re: Creating a trend function on Qlik

kaushiknsolanki — Wed, 13 Jul 2016 11:54:39 GMT

Hi,

Can you upload the excel file. It will be easy to guide you after looking at the file.

Regards,

Kaushik Solanki

Re: Creating a trend function on Qlik

qlikview979 — Wed, 13 Jul 2016 11:59:33 GMT

Hi marika,

please up lode your Excel file you will get response quickly.

Regards

Re: Creating a trend function on Qlik

marikabi — Fri, 15 Jul 2016 08:28:44 GMT

Hi,

thank you for your attention.

You can find the attached excel file in the original post

Once again thank you

Re: Creating a trend function on Qlik

marikabi — Fri, 15 Jul 2016 08:29:17 GMT

Hi,

thank you for your attention.

You can find the attached excel file in the original post

Once again thank you

Re: Creating a trend function on Qlik

qlikview979 — Fri, 15 Jul 2016 11:55:01 GMT

Hi marika,

Do you want like this right.

Dimention:-Region,Store

Expression:-Sum(QUANTITY SOLD)

Regards

Re: Creating a trend function on Qlik

marikabi — Fri, 15 Jul 2016 14:00:33 GMT

Hello,

I need to use as dimensione just the REGION.

But I need this expression for every month.

Then I need to analyze the trend (how the sales are evolving).

Thank you,

Regards

Re: Creating a trend function on Qlik

kaushiknsolanki — Sat, 16 Jul 2016 06:17:01 GMT

Hi,

Try script like below.

LOAD REGION,

STORE,

[QUANTITY SOLD],

'0' as Month

FROM

(ooxml, embedded labels, table is [CURRENT MONTH]);

LOAD REGION,

STORE,

[QUANTITY SOLD],

'1' as Month

FROM

(ooxml, embedded labels, table is [MONTH -1]);

LOAD REGION,

STORE,

[QUANTITY SOLD],

'2' as Month

FROM

(ooxml, embedded labels, table is [MONTH -2]);

LOAD REGION,

STORE,

[QUANTITY SOLD],

'3' as Month

FROM

(ooxml, embedded labels, table is [MONTH -3]);

LOAD REGION,

STORE,

[QUANTITY SOLD],

'4' as Month

FROM

(ooxml, embedded labels, table is [MONTH -4]);

LOAD REGION,

STORE,

[QUANTITY SOLD],

'5' as Month

FROM

(ooxml, embedded labels, table is [MONTH -5]);

Same for other months.

Then use the bar chart to show the trend. Keep Month as Dimension and Sun(Quantity Sold) as Expression.

Regards,

Kaushik Solanki

Re: Creating a trend function on Qlik

marikabi — Mon, 18 Jul 2016 07:38:32 GMT

Hi,

Thanks for your answer.

In this way I can see the trend just graphically; instead, I need to have a number to quantify the "slope" of the trend line.

Considering the amount of lines, I can't go through every REGION.

(the file I uploaded isn't the real file, I mean that the data are fake, and the column region contains more than just 4 different fields).

I need to create a sort of table where the columns I need are: REGION, TOTAL SALES FOR REGION in the different months, TREND (Considering all the months, for the same Region).

I think maybe I have to create a variable, or modify the script inserting a GROUP function.

But I have no knowledge about the SQL code, and I tried to do it in different ways without any good result.

Once again Thank you

Have a nice day,

cheers