topic Show Chart Data Based On Yearweek Range in QlikView

Show Chart Data Based On Yearweek Range

Anonymous — Mon, 12 Oct 2015 09:24:12 GMT

Hi, I have a question,

I have a qvd file that consists of some fields, one of them is YearWeek Field,

I want to load the data 4 weeks ago. Lets say today's YearWeek is 1542 (Year 2015 and Week 42), in this example i want to load data that has YearWeek between 1538 (1542-4) and 1541(1542-1).

The problem is i cant directly minus the yearweek with 4, because the year problem, let's say yearweek 1501 , the result i expect are yearweek between 1449,1450, 1451, 1452.

Is it possible to make it ?

Thanks, really appreciate your help

Re: Load Yearweek Range

sunny_talwar — Mon, 12 Oct 2015 09:26:35 GMT

What is the problem with using the solution provided to you by Digvijay here: Get Week Based on FileName

Re: Load Yearweek Range

sunny_talwar — Mon, 12 Oct 2015 09:29:37 GMT

I mean once you have them in a variable you can restrict a resident load with the variable you just created

LOAD blahblah

Resident Something

WHERE Match(YearWeek, $(var1), $(var2), $(var3), $(var4));

Re: Load Data Based On Yearweek Range

Anonymous — Tue, 13 Oct 2015 00:08:20 GMT

Hi Sunny,

Thanks for response.

i think i must change the title.

The problem is not in load statement. But in displaying the chart in qlikview dashboard. i have load the data, let's say 1530, 1531, 1532, 1533, 1534, 1535, 1536, 1537, 1538, 1539, 1540, 1541

When i choose 1541 on the list box, i want to show the data 4 months ago, so it will be 1538, 1539, 1540, 1541.

So i want to get the maximum value and minimum value to be used in my chart expression like this,

=sum({< YEARWEEK={'>=$(vYearMin) <=$(vYearMax)'}>}QTY)

do you have any idea ? because i cant find peek function in qlikview dashboard. (not in load statement)

Re: Load Data Based On Yearweek Range

sunny_talwar — Tue, 13 Oct 2015 00:31:36 GMT

I suggest creating a new column in your script called SortField

LOAD YEARWEEK,

AutoNumber(YEARWEEK) as SortField

FROM yourSource

Order By YEARWEEK;

and then may be this:

=Sum({<SortField = {"$(='>=' & (Max(SortField) - 3) & '<=' & (Max(SortField)))"}>} QTY)

Re: Show Chart Data Based On Yearweek Range

Anonymous — Tue, 13 Oct 2015 00:42:22 GMT

Hi Sunny,

do you mean AutoGenerate ?

I cant find AutoNumber function here.

it only has AutoGenerate.

Thanks

Re: Show Chart Data Based On Yearweek Range

sunny_talwar — Tue, 13 Oct 2015 00:59:27 GMT

Look at the below screenshot:

Re: Show Chart Data Based On Yearweek Range

Anonymous — Tue, 13 Oct 2015 01:21:54 GMT

Hi Sunny,

Okay, i already find that function.

And Yes, it solve my problem perfectly.

Thanks in advance Sunny

Re: Show Chart Data Based On Yearweek Range

sunny_talwar — Tue, 13 Oct 2015 02:15:47 GMT

Awesome

I am glad we finally got it working.

Best,

Sunny