Creating a trend function on Qlik

[marikabi]

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

[kaushiknsolanki]

Hi,

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

Regards,

Kaushik Solanki

[qlikview979]

Hi marika,

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

Regards

[marikabi]

Hi,

thank you for your attention.

You can find the attached excel file in the original post

Once again thank you

[marikabi]

Hi,

thank you for your attention.

You can find the attached excel file in the original post

Once again thank you

[qlikview979]

Hi marika,

Do you want like this right.

Dimention:-Region,Store

Expression:-Sum(QUANTITY SOLD)

Regards

[marikabi]

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

[kaushiknsolanki]

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

[marikabi]

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