Qlikview script where condition

[#1]

Hi,

I would like to select last 20 weeks from my base data using where /match condition. I have a field Week_ID, ranges between 1 to 52 for an year. I want select latest 20 weeks. I am trying to write something below. but getting error:

where Count(WEEK_ID)<=52 order by WEEK_ID desc;

Regards, please suggest.

[Colin-Albert]

You will also need to consider the start of the year, in week 1 of a new year, the past 3 weeks will be week 1 this year and week 52/51 of last year. (Or week 53/52 last year)

The best way to handle this is to create a field that combines year & week.

For example    (WkYear * 100) + WeekNo as YrWeekNo

And then apply Autonumber on this field, loading the data in ascending date order.

This will give a numeric sequence of weeks across all years.

   Autonumber(YrWeekNo) as YrWeekSeq

You can then select the last 3 weeks using a set expression on this sequence number

   Sum({<YrWeekSeq={“>$(=max(YrWeekSeq -3 ))“}>} Sales)

[maxgro · Accepted solution]

source:

load *inline [

Week, Sales

1, 100

2, 90

3, 80

4, 110

5, 120

6, 90

7, 80

];

max: load max(Week) as MaxWeek Resident source;

let vMaxWeek=peek('MaxWeek');

final: NoConcatenate load * Resident source where Week >= $(vMaxWeek)-2;

DROP Table source;

[#4]

Thanks,

This will not work if there are multiple rows for each week

//Method 2:

Table:
LOAD * INLINE [
Week, Sales
1, 100
2, 90
3, 80
4, 110
5, 120
5,100
6, 90
7, 80
];
Output

5,100

6,90

7,80

Correct Output

5,220

6,90

7,80

Thanks

[johnca]

Does it matter what the current week is? I mean, it sounds like you want the latest last 20 weeks not necessarily the last 20 weeks of the (full) year. Is that correct? If so then use something like

Sum({<Week={$(=Max(Week)-3)}>}Sales)

This gets the current year and any previous years' worth of data for those weeks. To get just the current year also add the year filter;

Sum({<Week={$(=Max(Week)-20)}, Year={$(=Max(Year))}>}Sales)

To make it varied add a variable to the mix;

Sum({<Week={$(=Max(Week)-vWeekCount)}, Year={$(=Max(Year))}>}Sales)

and create the variable vWeekCount in an input box or other.

HTH,

John

[sunny_talwar]

Yup, that makes sense. I guess some of the folks have a much better way for you to work with. I hope you get the answer you are looking for

Best,

S

[#7]

Hi Massimo, this is a correct answer and indeed a great learning.

[maxgro]

first 3 is interesting, maybe this

Table:

LOAD * INLINE [

    Week, Sales

    8, 1

    8, 11

    8, 111

    1, 100

    2, 90

    3, 80

    4, 110

    4, 1100   

    5, 120

    6, 90

    7, 80

    7, 800

];

Right Keep (Table)

First 3 LOAD Week, sum(1) as dropme

Resident Table

group by Week

Order By Week desc;

DROP field dropme;