topic Re: Future date(s) and variables in QlikView

Future date(s) and variables

Tue, 18 Aug 2015 08:10:26 GMT

Hello all,

Hopefully someone can help me with my problem. I want to make a forcast of some data.

I have: DayNumbers, WeekNumbers, MonthNumbers and YearNumbers in my qvw.

I want to create a forcast for one week ahead (now week 34 so want to see week 35). This works for now, but how do I create this if week 52 OR 53 goes to week 1. How do I set the year number +1 ?

What I did:

Variable: vNextWeek / =Week(Today())+1

Set analysis: sum({$<Categories={'SLA'}, WeekNumber={$(=vNextWeek)}, Afdeling={'Infrastructure System'}, YearNumber={'2015'}>}Gewerkte_Uren)

Thanks for thinking with me.

Re: Future date(s) and variables

jduarte12 — Tue, 18 Aug 2015 10:29:09 GMT

Hello.

Why don't you try to do something like:

if( week(today())<53, your set analysis, sum({$<Categories={'SLA'}, WeekNumber={1}, Afdeling={'Infrastructure System'}, YearNumber={'2016'}>}Gewerkte_Uren))

You could use your solution for the most part of the year and, in the last week, you would get the forecast for the first week of the following year.

Best regards,

João Duarte

Re: Future date(s) and variables

Tue, 18 Aug 2015 10:31:54 GMT

Thanks for your answer, this is something to consider, but then I need to update the qvw every year. My feeling says there must be a way to configure this and never look at it again.

Re: Future date(s) and variables

Tue, 18 Aug 2015 10:35:58 GMT

Why don't you try master calendar

Re: Future date(s) and variables

jduarte12 — Tue, 18 Aug 2015 10:43:17 GMT

Yes, for sure.

One I idea would be to make a similar step in the year part of the set analysis:

if( week(today())<53, your set analysis, sum({$<Categories={'SLA'}, WeekNumber={1}, Afdeling={'Infrastructure System'}, YearNumber={$('=year(today())+1'}>}Gewerkte_Uren))

Please check my set analysis for the year part, as I may have typed it wrongly.

Re: Future date(s) and variables

Tue, 18 Aug 2015 10:43:25 GMT

When I follow this guide: Creating A Master Calendarand use the right DateFields from my QVW am I at the right track?

Re: Future date(s) and variables

sasiparupudi1 — Tue, 18 Aug 2015 10:43:46 GMT

you could use YearNumber={$(=year(today()))}

or put year(today()) this into a variable

hth

Sasi

Re: Future date(s) and variables

Tue, 18 Aug 2015 10:45:21 GMT

This won't work when I run qlikview @ last week of December and then Week + 1 Then it wil do Week 1 of 2015, and I need week 1 of 2016

Re: Future date(s) and variables

Tue, 18 Aug 2015 10:48:45 GMT

I see where your going, but the answer of the if statement is always true, because the weeknumbers are always below 53. So it never gets to the last part of the statement, right?

Re: Future date(s) and variables

jduarte12 — Tue, 18 Aug 2015 10:55:21 GMT

Sorry.

I was meaning week 52; that should be the value on the set analysis I posted. If you run that set analysis on week 52 (the last week of a year), I think it should work.

Re: Future date(s) and variables

Peter_Cammaert — Tue, 18 Aug 2015 10:56:00 GMT

Maybe you shouldn't translate dates into year/month/week parts. Instead, stay with dates. The next week starts on WeekStart(Today()+7) and ends on WeekEnd(Today()+7). Those can be easily inserted as set analysis constraints.

Peter

Re: Future date(s) and variables

sasiparupudi1 — Tue, 18 Aug 2015 11:00:42 GMT

try

if(Weekyear(today())<year(Today()),year(Today()),Weekyear(today())

Re: Future date(s) and variables

Tue, 18 Aug 2015 11:12:05 GMT

Can you please help me where to put it in my set analysis

Re: Future date(s) and variables

sasiparupudi1 — Tue, 18 Aug 2015 11:23:46 GMT

YearNumber={"$(=if(Weekyear(today())<year(Today()),year(Today()),Weekyear(today())")}

Re: Future date(s) and variables

Tue, 18 Aug 2015 12:20:01 GMT

Unfortunately, this is not the solution.

Re: Future date(s) and variables

sasiparupudi1 — Tue, 18 Aug 2015 12:26:37 GMT

you might have to replace today() with max(yourdate) expression..

btw ,what is the issue?

Sasi

Re: Future date(s) and variables

Tue, 18 Aug 2015 12:33:29 GMT

Can you help me because I think this is what I need. I need all values from WeekStart(Today()+X) to WeekEnd(Today()+X) to be calculated as a SUM. So:

Day 1: 5
Day 2: 5
Day 3: 5

Day 4: 0

Day 5: 0
Day 6: 0
Day 7: 0
Total === 15

I've created to variables vWeekStart and vWeekEnd but I am unable to get the set analysis working. I tried: DateStart{">=$(vWeekEnd) <=$(vWeekStart)"}

DateStart is the field where al the dates are in.

Re: Future date(s) and variables

Anonymous — Tue, 18 Aug 2015 12:37:21 GMT

HI Bart,

Try as per below post:

Re: display/calculate weeks over years without actual date

its for past 12 weeks,modify as your requirement.

Regards

Neetha

Re: Future date(s) and variables

Peter_Cammaert — Tue, 18 Aug 2015 12:42:31 GMT

Off the top of my head, you could try with this as a start:

...[DateStart] = {'>=$(=WeekStart(Today()+7))<=$(=WeekEnd(Today()+7))'}...

Best,

Peter

Re: Future date(s) and variables

sasiparupudi1 — Tue, 18 Aug 2015 12:42:52 GMT

the syntax should be as follows.. you must disregard other date related filed selections like weeknumber and yearnumber if you filter the data on your transaction date

sum({$<Categories={'SLA'}, WeekNumber=, Afdeling={'Infrastructure System'}, YearNumber=,DateStart={">=$(vWeekEnd) <=$(vWeekStart)"}>}Gewerkte_Uren)

hth

Sasi