Average for Trend lines

[padmanabhan_ram]

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      

[sunny_talwar · Accepted solution]

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)))

[effinty2112]

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.

[padmanabhan_ram]

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

[padmanabhan_ram]

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