Weekly Metric

[Anonymous]

Hi,

I am working with a metric called trends that is on a weekly level, and others that are daily. The trends metric is pulled every Sunday which is the week start. Therefore, it results in null values for the rest of the week if other metrics are shown.

For example,

Keyword	Date	Trends	Impressions	Clicks
A	3/25/18	55	40	5
A	3/26/18	-	80	30
A	3/27/18	-	60	20
A	3/28/18	-	55	15
A	3/29/18	-	30	3

I want the table to show 55 for the rest of the week. I was thinking of creating a flag to take the week start trend value. Any thoughts?

Thanks!

[sunny_talwar]

Is this something you are looking to address in the script or front end of your app?

[Anonymous]

Hi Sunny,

Prefferably in the script but front end on the app works just as fine.

[sunny_talwar · Accepted solution]

May be something like this

FinalTable:

LOAD *,

     If(Keyword = Previous(Keyword) and Len(Trim(Trend)) = 0, Peek('New_Trend'), Trend) as New_Trend

Resident....

Order By Keyword, Date;

DROP Table ...;

[Anonymous]

I just tried this on the script and it's still showing the same nulls as the table above. Is there something I could use on the front of the app?

[sunny_talwar]

You might be able to, but I don't really know your setup... would you be able to share a sample?

[Anonymous]

Sunny,

Actually the script worked just fine! Thanks so much.

One other related question, so what if I have another trends table that pulls the daily value only for the past 30 days and we'll call it daily_trends table. The weekly_trends table above provides a weekly value but it goes back 5 years. What if I wanted to use the trends value from the daily_trends for the last 30 days but then use the trend value from weekly_trend table beyond 30 days. Would I just need to concatenate the two tables and use the same if statement?

daily_trends:

LOAD * INLINE [

keyword, date, trend_value

.. , .. , ..

A, 3/19/2018, 76

A, 3/20/2018, 25

A, 3/21/2018, 55

A, 3/22/2018, 65

A, 3/23/2018, 50

A, 3/24/2018, 40

A, 3/25/2018, 30

A, 3/26/2018, 50

A, 3/27/2018, 35

A, 3/28/2018, 70

];

weekly_trends:

LOAD * INLINE [

keyword, date, trend_value

A, 2/25/2018, 60

A, 2/18/2018, 35

A, 2/11/2018, 25

A, 2/4/2018, 80

A, 1/28/2018, 15

];

[sunny_talwar]

I believe so....