topic Re: Weekly Metric in QlikView

Weekly Metric

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

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!

Re: Weekly Metric

sunny_talwar — Thu, 29 Mar 2018 14:45:12 GMT

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

Re: Weekly Metric

Anonymous — Thu, 29 Mar 2018 14:48:41 GMT

Hi Sunny,

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

Re: Weekly Metric

sunny_talwar — Thu, 29 Mar 2018 14:50:30 GMT

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 ...;

Re: Weekly Metric

Anonymous — Thu, 29 Mar 2018 15:15:31 GMT

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?

Re: Weekly Metric

sunny_talwar — Thu, 29 Mar 2018 15:18:45 GMT

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

Re: Weekly Metric

Anonymous — Thu, 29 Mar 2018 16:02:43 GMT

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

];

Re: Weekly Metric

sunny_talwar — Thu, 29 Mar 2018 16:08:56 GMT

I believe so....