Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
wjtam206
Contributor
Contributor

Weekly Metric

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,

KeywordDateTrendsImpressionsClicks
A3/25/1855405
A3/26/18-8030
A3/27/18-6020
A3/28/18-5515
A3/29/18-303


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!

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

7 Replies
sunny_talwar

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

wjtam206
Contributor
Contributor

Hi Sunny,

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

sunny_talwar

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

View solution in original post

wjtam206
Contributor
Contributor

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?

wjtam206
Contributor
Contributor

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