Calculation of 8 Weeks ??

[#1]

Hi Everyone,

I have history data in WEEK field............I need to have last 8 weeks from it. I have another table which consists of latest week.....I need to pick this latest week and need to have the last 8 weeks from history table data................How can write an expression in QlikView to obtain the same...........

Thank You

[thomas_skariah]

Hi Gaurav,

Can you explain me the content in week field.

Regards,

Tom

[#3]

Hi Tom,

Thanx for Reply...........

The History table is a fact table which contain a field known as WEEK.

Week has contents like this:

WEEK

1

1

2

2

3

3

4

4

4

4.....

22

22

Again A table which contains the latest Week

the fields are

Refresh Date, Day , Week, Month

9/2/12, 3,22,8

This table contains the latest week

Now I need to pick the last 8 weeks from lastest week..........

for instance from 22, it will be from 15 to 22 (15,16,17,18,19,20,21,22)

when it will be 23, then 16 to 23.............

Can I have a expression for it?

[sivarajs]

Check attached file

Hope it helps you

--Siva

[#5]

Thanx Siva for ur Answer..........

The expression is working fine.............

but there is little bit change since I need to pic the latest date from one of the dimension table and match it to the history table and then obtain the last 8 weeks........

Here we are picking with the jhelp of max function..............

How could we achieve by matching the latest date from dimension table and obtaining the last 8 weeks from history table.............

Thank u !!

[sivarajs]

I have just added the date into the expression and converted into week

Check the file

[#7]

Hi Siva

Thanx for ur answers.............These answers are quite useful

but not fulfilling my need totally.

I am attaching the sample data(Excel sheet).
plz go through that and then u would clarify my doubt more easily.

The sample data contains 2 sheets:
1)Latest Date Table
2)Date Dimension Table

I need to MATCH the latest date from latest date table and then find the last 8 weeks
from date Dimension table.Plz help me this.

Waiting for ur reply. Thank u

[sivarajs]

Left join LATEST_DATE_TABLE with DATE_DIMENSION_TABLE will solve your issue

Example like

Load * from  DATE_DIMENSION_TABLE;

left join

load * from LATEST_DATE_TABLE;

[#9]

Hi Siva,

By doing the left join for the two table,

I got this type of table:

(plz see below)

How would it will solve my proble?

DATE_ID	Latest_Date	Day	Week	DATE	DAY	WEEK	Val
24	22/6/2012	5	10	22/6/2012	5	10	78

Can u Plz elaborate it? Thank u...........

[sivarajs]

DATE_ID is common between both the table

Alias Latest_date as DATE so that it will combine along with DATE

From DATE only you are going get recent date right?

Since max is used in expression it will fetch the recent one

Hope its clear