topic Re: min max dates in QlikView

min max dates

qvqfqlik — Sat, 09 Jul 2016 01:08:48 GMT

i need to find min and max dates for current week

LET vCW = peek('CYW',0,'table1');

LET vMinCurrentWk = (WeekStart($(vCW))));

LET vMaxCurrentWk = WeekDay(WeekEnd($(vCW)));

please help

vCW is returning current yearweek. No issue with this. But need to find min and max of current week.

Re: min max dates

tresB — Sat, 09 Jul 2016 09:19:13 GMT

How does your vCW (current yearweek) value look like? Is it a string or number?

Re: min max dates

swuehl — Sat, 09 Jul 2016 09:19:40 GMT

What's the format of your yearweek value? 'YYYYWW'?

You can use

MakeWeekDate(YYYY [ , WW [ , D ] ] )

function to create a date from your yearweek value:

LET vMinCurrentWk = MakeWeekDate(Left('$(vCW)',4),Right('$(vCW)',2));

LET vMaxCurrentWk = WeekDay(WeekEnd(MakeWeekDate(Left('$(vCW)',4),Right('$(vCW)',2))));

Re: min max dates

qvqfqlik — Sun, 10 Jul 2016 06:59:37 GMT

vCW looks like '201628' , i.e like 'YYYYWK' yearweek

I want to get min and max dates for current week.

table1:

NoConcatenate

LOAD

yrwk as CYW

Resident CAL

Order By@@

calYRWK

;

LET vCW= peek('CYW',0,'table1');

LET vMindateCWk = ?

LET vMaxdateCtWk = ?

please help

Re: min max dates

swuehl — Sun, 10 Jul 2016 15:58:21 GMT

Have you tried something like I've suggested above, using MakeWeekDate() function?

LET vMindateCWk = MakeWeekDate(Left('$(vCW)',4),Right('$(vCW)',2));

LET vMaxdateCtWk = DayName(WeekEnd(MakeWeekDate(Left('$(vCW)',4),Right('$(vCW)',2))));

Re: min max dates

maxgro — Sun, 10 Jul 2016 16:39:14 GMT

Maybe

WeekStart(MakeWeekDate(left('201628', 4), Right('201628', 2)))

WeekEnd(MakeWeekDate(left('201628', 4), Right('201628', 2)))

Replace 201628 with your field or variable

WeekStart/End return a timestamp

Re: min max dates

qvqfqlik — Sun, 10 Jul 2016 16:44:37 GMT

this will work only for 201628 , I guess what @swuehl solution will work, but I wan the Min date to start from Sunday, and Max date to be Saturday. Like current week would be Sun to Sat

Re: min max dates

qvqfqlik — Sun, 10 Jul 2016 16:46:20 GMT

Min date is 7/11/2016 which is monday...and max date is 7/17/2016

I need min date to start from Sunday

Also, within the script, how to calculatea metric for (current week + next 3 weeks) data -> next 3 weeks should be updated everyday. Please help

Re: min max dates

swuehl — Sun, 10 Jul 2016 18:10:07 GMT

MakeWeekDate() function uses ISO weeks.

If your week numbering matches ISO weeks, with just start of week being Sunday, you can just subtract 1 from the date that MakeWeekDate() returns (QlikView uses internally integer numbers to represent dates).

Re: min max dates

vinieme12 — Sun, 10 Jul 2016 18:24:10 GMT

If you need current weeks date why peek in to a table row?

LEt vMinWeekDate = WeekStart(now(),0,6); //Mondays Date

LEt vMaxWeekDate = WeekEnd(now(),0,6); // Sundays Date

Re: min max dates

maxgro — Sun, 10 Jul 2016 18:46:57 GMT

this will work only for 201628

Yes, I wrote in my post

Replace 201628 with your field or variable

I wan the Min date to start from Sunday, and Max date to be Saturday.

swuehl already answered (subtract 1 or redefine the week start)

Re: min max dates

qvqfqlik — Sun, 10 Jul 2016 19:14:49 GMT

Thankyou....and Also, within the script, how to calculate a metric for (current week + next 3 weeks) data -> next 3 weeks should be updated everyday. Please help

Re: min max dates

qvqfqlik — Sun, 10 Jul 2016 19:16:47 GMT

Thankyou. Calendar is not same as system calendar. Also, within the script, how to calculate a metric for (current week + next 3 weeks) data -> next 3 weeks should be updated everyday. Please help

Re: min max dates

vinieme12 — Sun, 10 Jul 2016 19:30:39 GMT

can you post a sample?

Re: min max dates

qvqfqlik — Sun, 10 Jul 2016 20:06:07 GMT

I am done with the current week part. I need to get next part.

I need to calculate a Dmnd metric for (current week+ next two weeks)

Current week demnd is snapshot of current week

next two weeks demnd have to be updated everyday

for current week demnd I tried to restrict the "where" condition as below

CWkdata:

NoConcatenate

LOAD

mat_key,

TODAY() as cal_key,

Sum( dd) as Dmnd // this is the metric

FROM

abcd.QVD (qvd)

Where

wkbeg >= $(vMinCurrentWk)

wkend <= $(vMaxCurrentWk)

Group By

mat_key, TODAY();

I have created two tables for and next week and the next to next week as below

Next Week

Caltable:

	NoConcatenate
	LOAD DISTINCT
	YRWK
	FROM calendr.QVD (qvd)
	Where CALDT = Today() + 7
	;

W1:

	NoConcatenate
	LOAD
	YRWK as Yw1
	Resident Caltable
	Order By
	YWK
	;

LET vW1 = peek('Yw1',0,'W1'); // this is calculating next week , i.e '201629'

DROP Table Caltable;

2 Weeks

Caltable:

	NoConcatenate
	LOAD DISTINCT
	YRWK
	FROM calendr (qvd)
	Where CALDT = Today() + 21;

W2:

NoConcatenate

LOAD

YRWK as Yw2

Resident Caltable

Order By

YRWK;

LET vW2 = peek('Yw2',0,'W2'); // this is calculating next week , i.e '201631'

DROP Table Caltable;

how to calculate next 2 weeks demnd to have to be updated everyday?

Please help

Re: min max dates

qvqfqlik — Sun, 10 Jul 2016 20:51:40 GMT

I need to calculate a Dmnd metric for (current week+ next two weeks)

Current week demnd is snapshot of current week

next two weeks demnd have to be updated everyday

for current week demnd I tried to restrict the "where" condition as below

CWkdata:

NoConcatenate

LOAD

mat_key,

TODAY() as cal_key,

Sum( dd) as Dmnd // this is the metric

FROM

abcd.QVD (qvd)

Where

wkbeg >= $(vMinCurrentWk)

wkend <= $(vMaxCurrentWk)

Group By

mat_key, TODAY();

I have created two tables for and next week and the next to next week as below

Next Week

Caltable:

	NoConcatenate
	LOAD DISTINCT
	YRWK
	FROM calendr.QVD (qvd)
	Where CALDT = Today() + 7
	;

W1:

	NoConcatenate
	LOAD
	YRWK as Yw1
	Resident Caltable
	Order By
	YWK
	;

LET vW1 = peek('Yw1',0,'W1'); // this is calculating next week , i.e '201629'

DROP Table Caltable;

2 Weeks

Caltable:

	NoConcatenate
	LOAD DISTINCT
	YRWK
	FROM calendr (qvd)
	Where CALDT = Today() + 21;

W2:

NoConcatenate

LOAD

YRWK as Yw2

Resident Caltable

Order By

YRWK;

LET vW2 = peek('Yw2',0,'W2'); // this is calculating next week , i.e '201631'

DROP Table Caltable;

how to calculate next 2 weeks demnd to have to be updated everyday?

Please help

Re: min max dates

qvqfqlik — Sun, 10 Jul 2016 20:52:10 GMT

I need to calculate a Dmnd metric for (current week+ next two weeks)

Current week demnd is snapshot of current week

next two weeks demnd have to be updated everyday

for current week demnd I tried to restrict the "where" condition as below

CWkdata:

NoConcatenate

LOAD

mat_key,

TODAY() as cal_key,

Sum( dd) as Dmnd // this is the metric

FROM

abcd.QVD (qvd)

Where

wkbeg >= $(vMinCurrentWk)

wkend <= $(vMaxCurrentWk)

Group By

mat_key, TODAY();

I have created two tables for and next week and the next to next week as below

Next Week

Caltable:

	NoConcatenate
	LOAD DISTINCT
	YRWK
	FROM calendr.QVD (qvd)
	Where CALDT = Today() + 7
	;

W1:

	NoConcatenate
	LOAD
	YRWK as Yw1
	Resident Caltable
	Order By
	YWK
	;

LET vW1 = peek('Yw1',0,'W1'); // this is calculating next week , i.e '201629'

DROP Table Caltable;

2 Weeks

Caltable:

	NoConcatenate
	LOAD DISTINCT
	YRWK
	FROM calendr (qvd)
	Where CALDT = Today() + 21;

W2:

NoConcatenate

LOAD

YRWK as Yw2

Resident Caltable

Order By

YRWK;

LET vW2 = peek('Yw2',0,'W2'); // this is calculating next week , i.e '201631'

DROP Table Caltable;

how to calculate next 2 weeks demnd to have to be updated everyday?

Please help

Re: min max dates

qvqfqlik — Mon, 11 Jul 2016 06:23:17 GMT

Could you please help me with this

Re: min max dates

qvqfqlik — Mon, 11 Jul 2016 06:23:36 GMT

Could you please help me with this

Re: min max dates

qvqfqlik — Mon, 11 Jul 2016 06:44:03 GMT

Please ignore typo... Its today()+14 for week2