topic Re: Calculation of 8 Weeks ?? in QlikView

Calculation of 8 Weeks ??

Wed, 05 Sep 2012 07:53:53 GMT

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

Re: Calculation of 8 Weeks ??

thomas_skariah — Wed, 05 Sep 2012 08:37:24 GMT

Hi Gaurav,

Can you explain me the content in week field.

Regards,

Tom

Re: Calculation of 8 Weeks ??

Wed, 05 Sep 2012 10:20:07 GMT

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

4.....

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?

Re: Calculation of 8 Weeks ??

sivarajs — Wed, 05 Sep 2012 11:28:49 GMT

Check attached file

Hope it helps you

--Siva

Re: Calculation of 8 Weeks ??

Wed, 05 Sep 2012 12:21:46 GMT

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

Re: Calculation of 8 Weeks ??

sivarajs — Thu, 06 Sep 2012 04:52:09 GMT

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

Check the file

Re: Calculation of 8 Weeks ??

Thu, 06 Sep 2012 07:29:00 GMT

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

Re: Calculation of 8 Weeks ??

sivarajs — Thu, 06 Sep 2012 07:47:58 GMT

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;

Re: Calculation of 8 Weeks ??

Thu, 06 Sep 2012 08:59:41 GMT

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

Re: Calculation of 8 Weeks ??

sivarajs — Thu, 06 Sep 2012 09:52:47 GMT

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