topic Re: help with metric calculation in QlikView

help with metric calculation

qvqfqlik — Sun, 10 Jul 2016 22:22:09 GMT

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

Current week dmnd is snapshot of current week

next two weeks dmnd have to be updated everyday

for current week dmnd 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: help with metric calculation

qvqfqlik — Mon, 11 Jul 2016 06:24:12 GMT

Could someone please help me with this

Re: help with metric calculation

varshavig12 — Mon, 11 Jul 2016 06:31:14 GMT

if current week is today()

week1 = today()+7

Then , week2 = today()+14 Right ?

Re: help with metric calculation

qvqfqlik — Mon, 11 Jul 2016 06:42:42 GMT

Yes... I wrote the 3rd week earlier... Please consider as today()+14

Re: help with metric calculation

varshavig12 — Mon, 11 Jul 2016 06:46:52 GMT

You can use Weekname(today()) for current week

and weekname(today(),2) for the next two weeks

Re: help with metric calculation

qvqfqlik — Tue, 12 Jul 2016 03:12:37 GMT

Question- What is meant by current week+next 2 weeks data ?

Why cant we call this as 3 weeks instead ?

Re: help with metric calculation

varshavig12 — Tue, 12 Jul 2016 03:20:49 GMT

I simply meant,

for eg: If today's week is 23

weekname(today(),2) will return 25.

Re: help with metric calculation

qvqfqlik — Tue, 12 Jul 2016 03:31:31 GMT

No , I was asking a question in general, as I have to calculate that metric for current week+next 2 weeks

Where current week snapshot is taken on monday and next two weeks should update everyday

Please help

Re: help with metric calculation

vinieme12 — Tue, 12 Jul 2016 03:43:05 GMT

Is the data available for the next two weeks or should they be forecasted?

Re: help with metric calculation

varshavig12 — Tue, 12 Jul 2016 03:45:34 GMT

I'm not very sure,

But you can try this in your code

wkbeg >= $(vMinCurrentWk) i.e weekStart(today())

wkend <= $(vMaxCurrentWk) i.e weekEnd(Weekname(today(),2))

Re: help with metric calculation

qvqfqlik — Tue, 12 Jul 2016 04:01:42 GMT

Data is available.. Wee need to see demand for current week+ next two weeks

Current week is based on snapshot taken on monday

And next two weeks should be update everyday

I already tried the logic with min week as curent week week and max week as week2 ... But im confused if its correct or not

Confused about "current week+newt two weeks".... Not sure why they simply call it three weeks ?

Re: help with metric calculation

qvqfqlik — Tue, 12 Jul 2016 05:52:47 GMT

How to write date ranges ?i was not ssking for metric calculation

Re: help with metric calculation

varshavig12 — Tue, 12 Jul 2016 06:19:28 GMT

If you want to filter your data for 3 weeks (current week + next 2 weeks )

Load *

from .....

where Your_date_field >=weekStart(today()) and Your_date_field <= weekEnd(Weekname(today(),2)) ;

Are you looking for something like this ?

Re: help with metric calculation

qvqfqlik — Tue, 12 Jul 2016 06:27:00 GMT

I tried this before... But I'm confused if my understanding of "current week + next 2 weeks" is same as the logic in thosevdate ranges.... Why dont they simply call this as 3 week demand?

Re: help with metric calculation

varshavig12 — Tue, 12 Jul 2016 06:33:42 GMT

Irrespective of what they say, the point is what they/you want to see as their/your output.

Only after once it is clear, we can try to help you in building the logic.

Re: help with metric calculation

qvqfqlik — Thu, 14 Jul 2016 01:55:06 GMT

could you please help me with this

Help with calculation-dates