Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Custom MonthStart and MonthEnd

Hi all,

I need to have custom monthstart and monthend Is this posible?

It completely depends on the week. Our week starts on Monday and ends on Sunday.

If the week starts on end of last month and ends on present month,  Then the monthstart date is that weekstart date for the current month.

And if monthend date will be checked on same basis it should be the last weekend date.

For example:

1)  October month : from sep 28th  to   Oct 25th.

2)  November month : from oct 26th  to Nov 29th.

3)  December month : from Nov 30th to Dec 27th.

Regards,

Pramod


14 Replies
balrajahlawat
Champion
Champion

Yes, you can..

What I would suggest, define a Inline function or a table in your database, like:

Load * inline

[Year, Month, MonthStart, MonthEnd

2015, Oct, 28th Sep 2015, 25th Oct 2015

2015, Nov, 26th Oct 2015, 29th Nov 2015

and so on..

];

Map this table into your data model, use intervalmatch of your tranxn date with this inline defined....

You will get what you want.

Not applicable
Author

Inline functions gives the date what we have hardcoded. please correct me if i wrong.

I will create a table like you explained.

Load * inline

[Year, Month, MonthStart, MonthEnd

2015, Oct, 28th Sep 2015, 25th Oct 2015

2015, Nov, 26th Oct 2015, 29th Nov 2015

and so on..

];

Then i need to enter all the start dates and end dates ii don't Want like that, It should create dynamically.

Is their any way to find the number of weekends in a month?

or

Number of sundays in a month?

balrajahlawat
Champion
Champion

Agreed, but no other solution

Not applicable
Author

Is their any way to find the number of weekends in a month?

or

Number of sundays in a month?

balrajahlawat
Champion
Champion

For number of sunday, you can try weekday(),Returns an integer between 0-6

like at script you can write like:

If(weekday(DateField)=6,'1','0') as SundayRecordCounter  //

and then simply write =sum(SundayRecordCounter)

tresesco
MVP
MVP

Read this blog post: Redefining the Week Start

jagan
MVP
MVP

Hi,

Try this sample script

Data:

LOAD

*,

Month(WeekEnd(Date)) AS Month;

LOAD

Date(YearStart(Today()) + RecNo()) AS Date

AutoGenerate(365);

Regards,

Jagan.

jagan
MVP
MVP

With Year

Data:

LOAD

*,

Year(WeekEnd(Date)) AS Year,

Month(WeekEnd(Date)) AS Month;

LOAD

Date(YearStart(Today()) + RecNo()-1) AS Date

AutoGenerate(365);

Regards,

jagan.

Not applicable
Author

need the weekend count of current month?