topic Re: Forecast Issue for date and Months in QlikView

Forecast Issue for date and Months

Anil_Babu_Samineni — Wed, 25 Nov 2020 16:16:04 GMT

Helo Friends,

I have the Data following in excel. So, here i want to create whole data for each month.

Let' assume

2014 - i have Months Jan, Feb, Mar, June -- Here, I want to add left of months i.e., Apr, May, July, Aug, Sep, Oct, Nov with all dates

2015 - Same

2016 - Should be up to to till date

For ref, Please find attachment of my excel

If i select 2014 i want to show 52 weeks same as 2015 and same as 2016 (But 2016 - I want to associate with only 27 weeks and left of 25 weeks should be hidden - Un associated with grey color)

Re: Forecast Issue for date and Months

PrashantSangle — Wed, 13 Jul 2016 07:16:39 GMT

Hi,

did you try with master calendar script.??

I think Master Calendar will help you to achieve your requirement.

Regards,

Re: Forecast Issue for date and Months

Anil_Babu_Samineni — Wed, 13 Jul 2016 07:19:00 GMT

Max,

Is there any chance to create that using my Excel, Please? Because, i am new to Qlik.

If possible, I request you that to create and then post that qvw to me so that i can implement my line

Thanks in Advance

Re: Forecast Issue for date and Months

abhijain — Wed, 13 Jul 2016 07:24:33 GMT

Hi,

Create Master Calendar first like

let vstart_date=num('01-01-2013');

let v_dateend=num(today());

calendar:

load $(vstart_date) + RecNo()-1 as Date autogenerate $(v_dateend)-$(vstart_date) +1;

And then create year and month from the date and do mapping with year ,month of data available

Re: Forecast Issue for date and Months

Anil_Babu_Samineni — Wed, 13 Jul 2016 07:36:57 GMT

Abhilasha,

This is for own calendar date. But my date already in excel. So, i need to add over there.

Please share me the App

Re: Forecast Issue for date and Months

PrashantSangle — Wed, 13 Jul 2016 07:41:19 GMT

Hi,

try below script

Facts:

LOAD S_CREATED_DATE

FROM

S_Created_Date.xls

(biff, embedded labels, table is Sheet1$);

MinMaxDate:

Load

Num(Min(S_CREATED_DATE)) as MinDate,

Num(Max(S_CREATED_DATE)) as MaxDate

Resident Facts;

Let vMinDate = Peek('MinDate',0,'MinMaxDate');

Let vMaxDate = Peek('MaxDate',0,'MinMaxDate');

Drop Table MinMaxDate;

Temp_calendar:

load $(vMinDate) + RecNo()-1 as Date autogenerate $(vMaxDate)-$(vMinDate) +1;

Master_calender:

Load MonthName(Date) as MonthYear,

Date(Date) as Date,

Year(Date) as Year,

Day(Date) as Day

Resident Temp_calendar;

drop Table Temp_calendar;

Regards

Re: Forecast Issue for date and Months

Anil_Babu_Samineni — Wed, 13 Jul 2016 07:52:40 GMT

Max,

Thanks. But, Usually default weeks are 52 only right, In my application it showing 53 weeks

Re: Forecast Issue for date and Months

PrashantSangle — Wed, 13 Jul 2016 08:22:17 GMT

Hi,

Yes for Year 2014 and 2015 there is 53 week because it consider weekstart from Moday to sunday.

You can redefine week structure. You will get 52 week if your 1st week is 1 Jan to 7 Jan

check article written by Henric on Week

Redefining the Week Start‌

Redefining the Week Numbers‌

Regards

Re: Forecast Issue for date and Months

Anil_Babu_Samineni — Wed, 13 Jul 2016 08:27:13 GMT

After that, I take the fields in list boxes, Which is S_CREATED_DATE and Dates why they are showing like that.

S_CREATED_DATE Should be start from 1st Jan to 31st Dec, Right while we creating the Master Calendar.

For ref, PFA

Re: Forecast Issue for date and Months

PrashantSangle — Wed, 13 Jul 2016 08:34:12 GMT

No,

You have to refer Date field from master calendar.

another for year 2014 your date start from 7-Jan-2014 because it is your min value in excel sheet.

If you also want to include 1-Jan-2014 to 6-Jan-2014 dates in file then in provided script do below modifications.

Num(YearStart(Min(S_CREATED_DATE))) as MinDate,

Regards,

Re: Forecast Issue for date and Months

Anil_Babu_Samineni — Wed, 13 Jul 2016 08:41:04 GMT

Max,

Not working. Here, Which field name i need to use for future calendar wither Quarters and all,

I need to use Date / S_CREATED_DATE?

- ANIL

Re: Forecast Issue for date and Months

PrashantSangle — Wed, 13 Jul 2016 09:21:17 GMT

Hi,

create association between S_CREATED_DATE and date from master Calendar.

and use date, Quarter,Week from master Calendar.

Please find below test script for your reference.

Facts:

LOAD Num(Floor(S_CREATED_DATE)) as Num_date,

S_CREATED_DATE

FROM

S_Created_Date.xls

(biff, embedded labels, table is Sheet1$);

MinMaxDate:

Load

Num(Yearstart(Min(S_CREATED_DATE))) as MinDate,

Num(Max(S_CREATED_DATE)) as MaxDate

Resident Facts;

Let vMinDate = Peek('MinDate',0,'MinMaxDate');

Let vMaxDate = Peek('MaxDate',0,'MinMaxDate');

Drop Table MinMaxDate;

Temp_calendar:

load $(vMinDate) + RecNo()-1 as Date autogenerate $(vMaxDate)-$(vMinDate) +1;

Master_calender:

Load Num(Date) as Num_date

,MonthName(Date) as MonthYear,

Date(Date) as Date,

Year(Date) as Year,

Day(Date) as Day

Resident Temp_calendar;

drop Table Temp_calendar;

If it wont solve your requirement, then please explain your entire requirement.

Regards,

Re: Forecast Issue for date and Months

Anil_Babu_Samineni — Wed, 13 Jul 2016 10:04:56 GMT

Max, Thanks. got it. For future level shall i use the same field (S_CREATED_DATE).

If i take two list boxes one for S_CREATED_DATE and another one is Date

If i select Year - The dates are changes from Date field not for the S_CREATED_DATE

So, In future i need to take Date Field as the Date. NP, I got it.

Thanks Max.