topic Re: Average for Trend lines in QlikView

Average for Trend lines

padmanabhan_ram — Fri, 04 Dec 2015 13:30:05 GMT

Hi All,

Hope you all are doing good.

I have attached the data for which I am trying to create a line graph with below specification. please help to create the same.

Primary dimension : WeekEndingdate

Secondary: ProductFranchise

Expression should show average growth for each 4 week rolling.

For example,

Week Growth

1 2.1

2 2.4

3 2.5

4 2.4

5 4.5

6 3.5

week 1 in the line graph should show me the growth value 2.1, week 2 will show 2.4, week 3 will show 2.5, while week 4 should give me the avg of change in growth between week 4, 3, 2,1 which is .1 if I am not wrong. Again week 5 should give avg of growth between week 5,4,3,2 and week 6 should give avg of growth between week 6,5,4,3.

Thanks in advance.

Padmanabhan

Re: Average for Trend lines

sunny_talwar — Fri, 04 Dec 2015 13:59:16 GMT

This?

Script: (Need to ensure correct sorting in the script to make it work)

Table:

LOAD WeekEndingDate as WED,

ProductFranchise as PF,

growth as g

FROM

[Data (2).xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD WED as WeekEndingDate,

PF as ProductFranchise,

g as growth

Resident Table

Order By PF, WED;

DROP Table Table;

Expression:

=If(RowNo() <= 3, Avg(growth), RangeAvg(Above(Avg(growth) - Above(Avg(growth)), 0, 3)))

Re: Average for Trend lines

effinty2112 — Fri, 04 Dec 2015 14:39:10 GMT

Hi Padmanabhan,

Your data has multiple rows for dates and franchise combinations. In the load I want to group by date and franchise but I don't know what the correct aggregation should be for the growth. For example for 14/11/2014, ProductFranchise B (174 rows) is the growth the sum of the growth column (24.82) or the average (0.14)?

Data:

LOAD

WeekEndingDate,

ProductFranchise,

sum(growth) as growth

FROM

Data.xlsx

(ooxml, embedded labels, table is Sheet1)

Group by WeekEndingDate, ProductFranchise;

gives this:

Data:

LOAD

WeekEndingDate,

ProductFranchise,

avg(growth) as growth

FROM

Data.xlsx

(ooxml, embedded labels, table is Sheet1)

Group by WeekEndingDate, ProductFranchise;

gives this:

I guess going by your expected result it must be the latter.

Re: Average for Trend lines

padmanabhan_ram — Tue, 08 Dec 2015 09:55:36 GMT

Hi Andrew,

I have the similar kind of issue in the below discussion, can you please try helping me.

4x4 week growth in line graph

Thanks,

Padmanabhan

Re: Average for Trend lines

padmanabhan_ram — Tue, 08 Dec 2015 09:56:51 GMT

Hi Sunny,

I have a similar kind of issue which has been mentioned in the below discussion, can you please try helping me.

4x4 week growth in line graph

Thanks,

Padmanabhan