Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
anat
Contributor

Calender Creation

@Team,

my requirement is to create calendar month starting day from Monday to Sunday,if month is end with other day except Sunday then it should calculate date from next month up to Sunday

for example if i select Jan-2016 (starting Monday in January is 4th & ending month 31st is Sunday),then data should fetch from 4th January2016 to 31st January 2016,

for example if i select Feb-2016 (starting Monday in February is 1st & ending month 29th is Monday ),then data should fetch from 1st Feb2016 to 6th March2016,

if i select May-2016 (starting Monday in May is 2nd  & ending month  31th  is Tuesday ),then data should fetch from 2nd May2016 to 5th June 2016,

if i select June-2016(starting Monday in June is 6th  & ending month  30th  is Thursday ),data should fetch from 6th June2016 to 3rd July 2016,

PFA.. sample data

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Calender Creation

Just create your calendar in excel. One excel file for the next 10 years and for the next 10 year you won't have to worry about it anymore. Simply load the excel file.

Or try this:

LET vStartDate = Num(MakeDate(2016));

LET vEndDate = MakeDate(2100)-1;

Calendar:

LOAD

  Date,

  Month(Date - Pick(WeekDay(Date)+1,0,1,2,3,4,5,6)) as Month

  ;

LOAD

  Date($(vStartDate) + RecNo() -1) as Date

AutoGenerate $(vEndDate) - $(vStartDate) + 1 ;


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
MVP & Luminary
MVP & Luminary

Re: Calender Creation

You already have your calendar in excel. Load the data from that excel file as your calendar.


talk is cheap, supply exceeds demand

Re: Calender Creation

for example if i select Jan-2016 (starting Monday in January is 4th & ending month 31st is Sunday),then data should fetch from 4th January2016 to 31st January 2016,

Where you want to show that, I mean Straight / Pivot

Take MonthYear as Dimension

And then try expression like this?


Sum({$<MonthYear = {">=" & $(=If(Day(MonthStart(YourDateFieldName)) = 'Sun', YourDateFieldName) "<=" &  $(=If(Day(MonthEnd(YourDateFieldName)) = 'Sun', YourDateFieldName)}>}Sales)


OR


Aggr(Sum({$<MonthYear = {">=" & $(=If(Day(MonthStart(MonthYear)) = 'Sun', YourDateFieldName) "<=" &  $(=If(Day(MonthEnd(MonthYear)) = 'Sun', YourDateFieldName)}>}Sales),MonthYear,YourDateFieldName)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
anat
Contributor

Re: Calender Creation

Actually that attachment is required output format.in source i am getting columns like

Datefielddaysales

so based on Datefield and day column i have to create Month column.

anat
Contributor

Re: Calender Creation

as per your logic :

if i select May2016 it's fetching data from 1st May 2016 to 29th May2016,but actually we need to fetch data   "from 2nd May2016 to 5th June 2016",

as per my requirement if i select May-2016 (starting Monday in May is 2nd  & ending month  31th  is Tuesday ),then data should fetch from 2nd May2016 to 5th June 2016,

MVP & Luminary
MVP & Luminary

Re: Calender Creation

Just create your calendar in excel. One excel file for the next 10 years and for the next 10 year you won't have to worry about it anymore. Simply load the excel file.

Or try this:

LET vStartDate = Num(MakeDate(2016));

LET vEndDate = MakeDate(2100)-1;

Calendar:

LOAD

  Date,

  Month(Date - Pick(WeekDay(Date)+1,0,1,2,3,4,5,6)) as Month

  ;

LOAD

  Date($(vStartDate) + RecNo() -1) as Date

AutoGenerate $(vEndDate) - $(vStartDate) + 1 ;


talk is cheap, supply exceeds demand

View solution in original post