Forecast Issue for date and Months

[Anil_Babu_Samineni]

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)

[PrashantSangle]

Hi,

did you try with master calendar script.??

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

Regards,

[Anil_Babu_Samineni]

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

[abhijain]

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

[Anil_Babu_Samineni]

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

[PrashantSangle]

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

[Anil_Babu_Samineni]

Max,

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

[PrashantSangle]

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

[Anil_Babu_Samineni]

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

[PrashantSangle]

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,